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
Showing posts with label object. Show all posts
Showing posts with label object. Show all posts
Tuesday, September 23, 2014
Script to list out all object level permissions in SQL Server
Subscribe to:
Posts (Atom)