Forgot Azure VM username or password?

Don't worry if you forgot your Azure VM Username/Password. Resetting your username and password is easy through Azure Portal

Below are the steps.

Step1: Go to Azure Portal and select your VM.

Step2: Under the options, you will find the Reset password option.

Step3: Enter your new username and password and click update on the top

Hurrah, now you can easily connect VM with your new username and password.

Important SQL Queries

 Here we will discuss some of the important SQL queries.

• Get Database list

        SELECT name as DB_Name,create_date as CreatedDate
        FROM sys.databases

• Get row count in all the Database tables

           SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' +                                                     QUOTENAME(sOBJ.name) AS [TableName]
           ,SUM(sPTN.Rows) AS [RowCount]
           FROM  sys.objects AS sOBJ  INNER JOIN sys.partitions AS sPTN
           ON sOBJ.object_id = sPTN.object_id
           WHERE sOBJ.type = 'U'    AND sOBJ.is_ms_shipped = 0x0
           AND index_id < 2 -- 0:Heap, 1:Clustered
           GROUP BY sOBJ.schema_id , sOBJ.name
           ORDER BY [TableName]

• Recent Modified stored procedures and tables

        SELECT name, create_date, modify_date ,type
        FROM sys.objects
        WHERE type = 'P' or  type = 'U'
        ORDER BY modify_date DESC

• The last query run on the database

          SELECT  text,*
          FROM    sys.dm_exec_query_stats AS deqs
          CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
          WHERE   deqs.last_execution_time > '5/16/2022'
          order by creation_time desc

• Check user roles in the database

          SELECT ServerName=@@servername, dbname=db_name(db_id()),
           p.name as UserName, p.type_desc as TypeOfLogin, 
          pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
          FROM sys.database_role_members roles
          JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
          JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

• CTE for Recursive Query

    WITH CTE AS      
(        
    SELECT 1 Number        
    UNION ALL        
    SELECT Number + 1 FROM CTE WHERE Number<20        
) 
  SELECT * FROM CTE

• Create a new user from the query

        CREATE LOGIN [ServiceUser] WITH PASSWORD = 'Admin@27'
        CREATE USER [ServiceUser] FOR LOGIN [ServiceUser] WITH DEFAULT_SCHEMA=[dbo]

Note: Run Create Login query on Master DB, Run User Query on specific DB access.
If you want to create a new login for an Azure AD user then run the below query on Master DB
        

    CREATE LOGIN [ademail] from external provider;

If you want to create a user in the specific DB then use the below query
CREATE USER [User1] WITH PASSWORD = 'Test@12', DEFAULT_SCHEMA = dbo; 


• Assign Roles to newly created User

        EXEC sys.sp_addrolemember 'db_datareader', 'serviceuser';
        EXEC sys.sp_addrolemember 'db_datawriter', 'serviceuser'; 
        EXEC sys.sp_addrolemember 'db_owner', 'ServiceUser';

• While loop

      DECLARE @ComponentCode int=0

      WHILE EXISTS (SELECT 1 FROM QualityList WHERE QualityId > @ComponentCode)
      BEGIN          
        SELECT @ComponentCode = MIN(QualityId) FROM QualityList 
	  WHERE QualityId > @ComponentCode          
       
	  SELECT @ComponentCode 
      END

• Calculate SQL/Azure SQL Database Size

        SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024  AS DbSizeInMB
FROM sys.dm_db_partition_stats

AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access

 How to Get Access Token from Postman when you have an MFA enabled in the AD

First, Let's learn how to get an access token if MFA is not enabled.
To get the token you need to pass the below parameters.

• grant_type (password - hard code)
• client_id
• client_secret
• scope (user.read)
• username (AD username)
• password (AD password)

For TenantId, ClientId and Client_Secret. You can log in to the Azure portal and then the Azure active directory --> App Registrations --> Check your app registration or add new registration.

Once you call this method, you will get the access token.

Note: If MFA is enabled then you will get the below error.

To solve this issue, you need to do the below steps.

Step 1: You need to open the URL in the browser mentioned below. Make sure to add your tenantId, clientId, and redirect URL. Redirect URL, you can set any value in the redirect URL in App Registration of Azure Portal and pass the same redirect URL here.

https://login.microsoftonline.com/{{tenantId}}/oauth2/v2.0/authorize?
client_id={{clientId}}
&response_type=code
&redirect_uri=https://localhost:44321/
&response_mode=query
&scope=https://graph.microsoft.com/User.Read
&state=12345

The below snapshot is just to explain to you that you can get a set redirect URL in App Registration.

Step 2: Copy the URL and paste it into the notepad. You will see this URL has 3 parameters 

• Code
• State
• Session_state

Copy only the code part.

https://localhost:44321/?code=0.AUUAV8CEcnhAcEGSwBvLEcVSaUBShxdFPBtDqBWzMDbIrtRFAH4.AQABAAIAAAD--DLA3VO7QrddgJg7WevrOJWL51jAIIL76f1jPOMwfGpCGDOkHmxnYhixC2A4SFRJE5cf_AOpaELqjrxJA-MbPvCIBDfWLZtjo2zVw2AQ2CfagTN2gMsErjuSsTzsFNwg7AGEMBZ1D_hpekpVjwS7OpliDUAL1iS4fiUq0iWWbDZNohQFFtQbgmKLh-EKCuyFRyqMSiZZPOW20S6J-6r6TCB-SPMEG7RrlsWaXMxLuSJpripTV9_4FlXJg0oZmvogiGmuVQ9U1ckhTsMZmFps4F-3PNxEZRXAvLFEbDQPP5KnB8zGKGB_px5B5m1NTDkKhck9-WW0yTVeJiuyTmbtofvu1exYQLJv8we6F11rUaEG6ogtW6qAZQbOElIrtNQ_aYQ5Gkd9KUnJNFOFGmtEsVXAhxVfFZ6uFLio6TzjcDm12u_CUNeewDCrR5QuOK1JrDeq2yjHx3lD-h0SMAFD6CG5K6vqpwgv7MqVPONJas09AvD3rQpoFgpGuSF997NJiCguYPkXpW6fYYH9-1aJE9Qob3GGzlcSCTO0OBseN95oprKTT4246scT-_VsOXMca36bgPOsNDPMRfxTy9nSZaZmugJDxlijfKEUXcYbFtUZjKD2Cz3OQC1rGJF03db7cOdYuRGCSTy-C28CnYikIAA&state=12345&session_state=9e429398-8f47-4210-a2b4-72866178d144#

Step 3: Call the endpoint again in the postman. This time parameters are different. We need to pass below 5 parameters. Code parameter is where we need to paste that code.

• grant_type (authorization_code - hard code)
• client_id
• client_secret
• code (authorization code that you recently get from the browser)
• redirect_uri (same that you passed in the above parameter)

Once you will send the request, you can see you have an access token.

Now you have your access token. I hope you like the article. Thank you.