Azure SQL - Useful Queries

When working with Azure SQL using SQL server management studio (SSMS), most of the features we used to get through SSMS GUI while connecting with OnPre/IaaS SQL-Server are not available (e.g.: get the user's role assignment, create user/role ..) Here are a few useful T-SQL queries:

Introduction 

 

While working with Azure SQL using SQL server management studio (SSMS), most of the features which we used to get through SSMS GUI while connecting with OnPre/IaaS SQL-Server are not available (e.g.: get the user's role assignment, create user/role ..)

 

Here are a few useful T-SQL queries.

 

To create a role:

 

First, check if we have the given role existing in the database or not. If it doesn't exist, then let's create it. 

IF DATABASE_PRINCIPAL_ID('<<your-role-name>>') IS NULL
BEGIN
  -- Add Role here
  CREATE ROLE [<<your-role-name>>];
END

To create a user:

 

First, check if the given user is present in the database or not. If not then let's create the user.

-- Create User if not exists [<<your-user-name>>]
IF NOT EXISTS (SELECT  [name]
            FROM
            sys.database_principals
            WHERE   [name] = '<<your-user-name>>'   )
BEGIN
   CREATE USER [<<your-user-name>>] FROM  EXTERNAL PROVIDER ;
END

Get user, its description & grant permission summary

SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc,
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

To get the role detail

SELECT
    drs.role_principal_id
    , drs.member_principal_id
    , dp_role.name as  "role_name"
    , dp_role.type_desc as "role_type_desc"
    , dp_role.type as "role_type"
    , dp_member.name  as "member_name"
    , dp_member.type as "member_type"
    , dp_member.type_desc as "member_type_desc"
    , dp_member.authentication_type as "member_authentication_type"
    , dp_member.authentication_type_desc as "member_authentication_type_desc"
FROM sys.database_role_members as drs
    LEFT JOIN sys.database_principals as dp_role
ON drs.role_principal_id = dp_role.principal_id
    LEFT JOIN sys.database_principals as dp_member
ON drs.member_principal_id = dp_member.principal_id
    WHERE dp_member.name = N'<<your-user-name>>'
Order by 1

To get the referenced or referencing object. Sometimes we need to get all the object lists that are dependent on the given stored procedure or to get the object on which the given stored procedure is dependent.

 

To get the object dependencie frequently, use the stored procedure: "sp_depends"

sp_depends '<your-schema>.<your-sp-name>'
-- 1st Result: Object on which it depends
-- 2nd Result: Object which depends on this

A better way to see the object dependencies is to use dm_sql_referenced_entities and dm_sql_referencing_entities

-- Object on which this sp depends
SELECT
        referenced_schema_name,
        referenced_entity_name,
        referenced_minor_name,
        referenced_minor_id,
        referenced_class_desc,
        is_caller_dependent,
        is_ambiguous
FROM
    sys.dm_sql_referenced_entities ('your-schema>.<your-sp-name>',
        'OBJECT')

-- Object which depends on this sp
SELECT referencing_schema_name,
    referencing_entity_name,
    referencing_id,
    referencing_class_desc,
    is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<your-schema>.<your-sp-name>',
        'OBJECT');