Tuesday, September 23, 2014

Script to list out all object level permissions in SQL Server

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) + CASE 
  WHEN cl.column_id IS NULL
   THEN SPACE(0)
  ELSE '(' + QUOTENAME(cl.NAME) + ')'
  END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Object Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
 AND cl.[object_id] = PERM.major_id
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Database Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE PERM.major_id = 0
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

No comments:

Post a Comment