--1)List all access provisioned to a SQL user or Windows user / group directly
SELECT
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQLUser'
WHEN 'U' THEN 'WindowsUser'
WHEN 'G' THEN 'WindowsGroup'
WHEN 'E' THEN 'ExternaluserfromAzureActiveDirectory'
WHEN 'X' THEN 'ExternalgroupfromAzureActiveDirectorygrouporapplications'
END,
[DatabaseUserName] = princ.[name],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] --Schema-containedobjects
ELSE perm.[class_desc] --Higher-levelobjects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] --Schemas
WHEN 4 THEN imp.[name] --Impersonations
ELSE OBJECT_NAME(perm.[major_id]) --Generalobjects
END,
[ColumnName] = col.[name]
FROM
--Databaseuser
sys.database_principals AS princ
--Loginaccounts
LEFTJOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
LEFTJOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFTJOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFTJOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFTJOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Tablecolumns
LEFTJOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFTJOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G', 'E', 'X')
--Noneedforthesesystemaccounts
AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
--2)List all access provisioned to a SQL user or Windows user / group through a database or application role
SELECT
[UserType] = CASE membprinc.[type]
WHEN 'S' THEN 'SQLUser'
WHEN 'U' THEN 'WindowsUser'
WHEN 'G' THEN 'WindowsGroup'
WHEN 'E' THEN 'ExternaluserfromAzureActiveDirectory'
WHEN 'X' THEN 'ExternalgroupfromAzureActiveDirectorygrouporapplications'
END,
[DatabaseUserName] = membprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] --Schema-containedobjects
ELSE perm.[class_desc] --Higher-levelobjects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] --Schemas
WHEN 4 THEN imp.[name] --Impersonations
ELSE OBJECT_NAME(perm.[major_id]) --Generalobjects
END,
[ColumnName] = col.[name]
FROM
--Role/memberassociations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Rolemembers(databaseusers)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Loginaccounts
LEFTJOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--Permissions
LEFTJOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFTJOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFTJOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFTJOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Tablecolumns
LEFTJOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFTJOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN ('S','U','G', 'E', 'X')
--Noneedforthesesystemaccounts
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDERBY
[UserType],
[DatabaseUserName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType];