As a Database Administrator, you may need to identify the permissions that have been granted to a user-created database role. To begin, use the query provided by Microsoft to list all of the database roles that are currently present in the database.
Ref. sys.database_role_members
The following query returns the members of the database roles.
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' ORDER BY DP1.name;
Now to check the list of all Permissions for a given user DB role use the below query
SELECT DB_NAME() AS DatabaseName ,DatabasePrincipals.name AS PrincipalName ,DatabasePrincipals.type_desc AS PrincipalType ,DatabasePrincipals2.name AS GrantedBy ,DatabasePermissions.permission_name AS Permission ,DatabasePermissions.state_desc AS StateDescription ,SCHEMA_NAME(SO.schema_id) AS SchemaName ,SO.Name AS ObjectName ,SO.type_desc AS ObjectType FROM sys.database_permissions DatabasePermissions LEFT JOIN sys.objects SO ON DatabasePermissions.major_id = so.object_id LEFT JOIN sys.database_principals DatabasePrincipals ON DatabasePermissions.grantee_principal_id = DatabasePrincipals.principal_id LEFT JOIN sys.database_principals DatabasePrincipals2 ON DatabasePermissions.grantor_principal_id = DatabasePrincipals2.principal_id WHERE DatabasePrincipals.name = 'db_executor' -- Change the Role Name
Leave a Reply