Database Permissions Script

DatabasePermissions.zip

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).

SELECT
 CASE
  WHEN P.class IN (0, 3) THEN P.class_desc
  ELSE O.type_desc
 END type_desc,
 CASE
  WHEN P.class IN (0, 3) THEN ''
  ELSE SCHEMA_NAME(O.schema_id)
 END object_schema,
 CASE
  WHEN P.class = 3 THEN SCHEMA_NAME(P.major_id)
  WHEN P.class = 1 THEN OBJECT_NAME(P.major_id)
  ELSE ''
 END object_name,
 ISNULL(C.name, '') column_name,
 P.class_desc,
 USER_NAME(P.grantee_principal_id) grantee,
 P.permission_name,
 P.state_desc,
 USER_NAME(P.grantor_principal_id) grantor,
 CASE P.state
  WHEN 'W' THEN 'GRANT'
  ELSE P.state_desc
 END + ' ' +
 CASE
  WHEN P.class = 0 THEN P.permission_name + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default
  WHEN P.class = 3 THEN P.permission_name + ' ON SCHEMA::' + QUOTENAME(SCHEMA_NAME(P.major_id)) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  WHEN P.minor_id <> 0 THEN P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' (' + QUOTENAME(C.name) + ') TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  ELSE P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
 END +
 CASE P.state
  WHEN 'W' THEN ' WITH GRANT OPTION'
  ELSE ''
 END script
FROM
 sys.database_permissions P
  LEFT JOIN
 sys.all_objects O ON
  O.object_id = P.major_id
  LEFT JOIN
 sys.all_columns C ON
  C.object_id = P.major_id AND
  C.column_id = P.minor_id
WHERE
 NOT
 (
  P.class = 1 AND
  (
   EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') OR
   O.is_ms_shipped = 1
  )
 )
ORDER BY
 1,2,3,4,5,6,7,8