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
Tuesday, September 23, 2014
Script to list out all object level permissions in SQL Server
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment