Listing all the permissions in an Azure SQL Database

Azure SQL Database has multiple different ways of setting user permissions so it can get difficult to list them all. In this post I’ll try to cover them. The T-SQL queries are located in this repo and there are also SQL Server versions to list logins in addition to users.

Database roles

Database roles are a fairly simple way of giving permissions. db_owner, db_datareader and db_datawriter are probably the most common ones. The following query lists all of them in a database.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, '-') AS PrincipalName,
    DP2.type_desc AS PrincipalType
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
    AND DP2.name IS NOT NULL
ORDER BY DP1.name, DP2.name
List of database roles
List of database roles

Grant and deny

Using GRANT and DENY commands enables a granular way of giving permissions but it can also get quite messy over time if there are multiple users and a large amount of database objects. This query lists them for a database.

SELECT DISTINCT
    DB_NAME() AS 'DBName',
    p.[name] AS 'PrincipalName',
    p.[type_desc] AS 'PrincipalType',
    p2.[name] AS 'GrantedBy',
    dbp.[state_desc] AS StateDescription,
    dbp.[permission_name] AS PermissionName,
    dbp.class_desc AS ClassDescription,
    (CASE
        WHEN class_desc = 'DATABASE' THEN DB_NAME(dbp.major_id)
        WHEN class_desc = 'SCHEMA' THEN SCHEMA_NAME(dbp.major_id)
        ELSE ''
	END) AS AppliesTo,
	dbp.major_id AS AppliesToID,
	ISNULL(SCHEMA_NAME(so.schema_id), '') AS 'ObjectSchema',
	ISNULL(so.[Name], '') AS 'ObjectName',
    ISNULL(so.[type_desc], '') AS 'ObjectType'
FROM [sys].[database_permissions] dbp
LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id]
LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id]
LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]
WHERE p.name != 'public'
ORDER BY p.name, AppliesTo
List of database GRANTs
List of database GRANTs

Schema ownership

Giving ownership of database schemas enables large permissions only for the objects in that schema. It can be useful when for example you need to enable table creation permissions in only one schema. This query lists the owners.

SELECT s.Name as SchemaName, u.name AS [Owner]
FROM sys.schemas s
INNER JOIN sys.sysusers u
    ON u.uid = s.principal_id
INNER JOIN sys.database_principals dp
    ON s.principal_id = dp.principal_id
WHERE s.Name NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
    AND dp.type = 'S'
ORDER BY s.Name, u.name
List of schema owners
List of schema owners

Conclusion

That should cover the most common methods of giving permissions in an Azure SQL Database. The scripts are available in the repo.