Azure SQL - Useful queries

While working with Azure SQL using SQL server management studio (SSMS), most of the feature which we used to get through SSMS GUI while connecting with OnPre/IaaS SQL-Server, is 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

Create Role

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

To create a 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 [<>] FROM  EXTERNAL PROVIDER ;
END
  

Get user role & grant permission summary

Role & 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;

--Role sumamry
SELECT 
	dp_role.name as db_role_name,
	dp_member.name as db_member_name
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 object's dependencies

--Generally used sp_depends

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

-- Object on which this sp depnds
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>.<our-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>.<our-sp-name>', 
		'OBJECT');