I have a role named
db_executor and need to find out what permissions it has on what objects. I found List all permissions for a given role? but the only solution that remotely works is:
SELECT DB_NAME() AS 'DBName' ,p.[name] AS 'PrincipalName' ,p.[type_desc] AS 'PrincipalType' ,p2.[name] AS 'GrantedBy' ,dbp.[permission_name] ,dbp.[state_desc] ,so.[Name] AS 'ObjectName' ,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] = 'db_executor'
The problem is that the
NULL. So while I know it only has the
EXECUTE permission in a state of
GRANT I don't know to what object(s) that applies to. Is there a better way to get this list or how can I modify this code to list the Object(s)?