Monday, June 22, 2015

Query to get details of permissions on Database objects

Use below query to get details of permissions on Database objects

SELECT AS [User Name],
CASE princ.type WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' WHEN 'S' THEN 'SQL User' END AS [User Type], AS [Database User Name], perm.permission_name AS [Permission Type], perm.state_desc AS [Permission State],
CASE perm.class WHEN 1 THEN obj.type_desc ELSE perm.[class_desc] END AS [Object Type],
CASE perm.class WHEN 1 THEN OBJECT_NAME(perm.major_id) WHEN 3 THEN schem.[name] WHEN 4 THEN imp.[name] END AS [Object Name], AS [Column Name]
FROM sys.database_principals princ LEFT JOIN sys.server_principals ulogin on princ.sid = ulogin.sid LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id  LEFT JOIN sys.database_principals imp ON imp.principal_id = perm.major_id LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id  LEFT JOIN sys.schemas schem ON schem.schema_id = perm.major_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE princ.[type] IN ('S','U','G') AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

No comments:

Post a Comment