Thursday, June 9, 2011

TSQL to list all Permissions within a Database

Came across a situation today while trouble shooting a problem with replication where the subscription database has continually been getting modified by an unknown source and thus breaking replication. To assist with investigating this issue there was a need to look at all the permissions assigned to all users and roles within the database both at the Publisher and the Subscriber..... so here is the TSQL I wrote to help with this.

select sys.database_permissions.class_desc
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, sys.database_principals.name as [grantee_principal_name]
, ISNULL(sys.schemas.name, '') as [schema_name]
, ISNULL(AllObjects.name, '') as [object_name]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'GRANT ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'GRANT ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Grant_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'DENY ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'DENY ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Deny_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'REVOKE ' + sys.database_permissions.permission_name + ' FROM [' + sys.database_principals.name + '];'
ELSE 'REVOKE ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] FROM [' + sys.database_principals.name + '];'
END) as [Revoke_Perms_Stmnt]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
--AND sys.database_principals.name = 'public' --uncomment this line to restrict the output to a single principal
LEFT JOIN (
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.objects
UNION
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
ORDER BY sys.database_principals.name
, sys.database_permissions.class
, sys.schemas.name
, AllObjects.name