This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [DATENBANK] | |
SELECT | |
[UserName] = CASE princ.[type] | |
WHEN 'S' THEN princ.[name] | |
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI | |
END, | |
[UserType] = CASE princ.[type] | |
WHEN 'S' THEN 'SQL User' | |
WHEN 'U' THEN 'Windows User' | |
END, | |
[DatabaseUserName] = princ.[name], | |
[Role] = null, | |
[PermissionType] = perm.[permission_name], | |
[PermissionState] = perm.[state_desc], | |
[ObjectType] = obj.type_desc,--perm.[class_desc], | |
[ObjectName] = OBJECT_NAME(perm.major_id), | |
[ColumnName] = col.[name] | |
FROM | |
--database user | |
sys.database_principals princ | |
LEFT JOIN | |
--Login accounts | |
sys.login_token ulogin on princ.[sid] = ulogin.[sid] | |
LEFT JOIN | |
--Permissions | |
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] | |
LEFT JOIN | |
--Table columns | |
sys.columns col ON col.[object_id] = perm.major_id | |
AND col.[column_id] = perm.[minor_id] | |
LEFT JOIN | |
sys.objects obj ON perm.[major_id] = obj.[object_id] | |
WHERE | |
princ.[type] in ('S','U') | |
UNION | |
--List all access provisioned to a sql user or windows user/group through a database or application role | |
SELECT | |
[UserName] = CASE memberprinc.[type] | |
WHEN 'S' THEN memberprinc.[name] | |
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI | |
END, | |
[UserType] = CASE memberprinc.[type] | |
WHEN 'S' THEN 'SQL User' | |
WHEN 'U' THEN 'Windows User' | |
END, | |
[DatabaseUserName] = memberprinc.[name], | |
[Role] = roleprinc.[name], | |
[PermissionType] = perm.[permission_name], | |
[PermissionState] = perm.[state_desc], | |
[ObjectType] = obj.type_desc,--perm.[class_desc], | |
[ObjectName] = OBJECT_NAME(perm.major_id), | |
[ColumnName] = col.[name] | |
FROM | |
--Role/member associations | |
sys.database_role_members members | |
JOIN | |
--Roles | |
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] | |
JOIN | |
--Role members (database users) | |
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] | |
LEFT JOIN | |
--Login accounts | |
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] | |
LEFT JOIN | |
--Permissions | |
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] | |
LEFT JOIN | |
--Table columns | |
sys.columns col on col.[object_id] = perm.major_id | |
AND col.[column_id] = perm.[minor_id] | |
LEFT JOIN | |
sys.objects obj ON perm.[major_id] = obj.[object_id] | |
UNION | |
--List all access provisioned to the public role, which everyone gets by default | |
SELECT | |
[UserName] = '{All Users}', | |
[UserType] = '{All Users}', | |
[DatabaseUserName] = '{All Users}', | |
[Role] = roleprinc.[name], | |
[PermissionType] = perm.[permission_name], | |
[PermissionState] = perm.[state_desc], | |
[ObjectType] = obj.type_desc,--perm.[class_desc], | |
[ObjectName] = OBJECT_NAME(perm.major_id), | |
[ColumnName] = col.[name] | |
FROM | |
--Roles | |
sys.database_principals roleprinc | |
LEFT JOIN | |
--Role permissions | |
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] | |
LEFT JOIN | |
--Table columns | |
sys.columns col on col.[object_id] = perm.major_id | |
AND col.[column_id] = perm.[minor_id] | |
JOIN | |
--All objects | |
sys.objects obj ON obj.[object_id] = perm.[major_id] | |
WHERE | |
--Only roles | |
roleprinc.[type] = 'R' AND | |
--Only public role | |
roleprinc.[name] = 'public' AND | |
--Only objects of ours, not the MS objects | |
obj.is_ms_shipped = 0 | |
ORDER BY | |
princ.[Name], | |
OBJECT_NAME(perm.major_id), | |
col.[name], | |
perm.[permission_name], | |
perm.[state_desc], | |
obj.type_desc--perm.[class_desc] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment