I have been struggling every time I needed to retrieve all the users, roles, and the associated permisisons that I have created since Azure SQL is a little different from SQL Server I am used to when it comes to user administration. So I came up with this query that I'd like to share:
Adding principal_id > 4 ensures removal of dbo, public etc...
SELECT p.[name] as 'Principal_Name', CASE WHEN p.[type_desc]='SQL_USER' THEN 'User' WHEN p.[type_desc]='DATABASE_ROLE' THEN 'Role' END As 'Principal_Type', --principals2.[name] as 'Grantor', dbpermissions.[state_desc] As 'Permission_Type', dbpermissions.[permission_name] As 'Permission', CASE WHEN so.[type_desc]='USER_TABLE' THEN 'Table' WHEN so.[type_desc]='SQL_STORED_PROCEDURE' THEN 'Stored Proc' WHEN so.[type_desc]='VIEW' THEN 'View' END as 'Object_Type', so.[Name] as 'Object_Name' FROM [sys].[database_permissions] dbpermissions LEFT JOIN [sys].[objects] so ON dbpermissions.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p ON dbpermissions. [grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] principals2 ON dbpermissions.[grantor_principal_id] = principals2.[principal_id] WHERE p.principal_id > 4
Adding principal_id > 4 ensures removal of dbo, public etc...
No comments:
Post a Comment