Azure SQL - Useful 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');