--1) List all access provisioned
to a SQL user or Windows user/group directly
SELECT
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL
User'
WHEN 'U' THEN 'Windows
User'
WHEN 'G' THEN 'Windows
Group'
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN
obj.[type_desc] --
Schema-contained objects
ELSE
perm.[class_desc] --
Higher-level objects
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]) -- General
objects
END,
[ColumnName] = col.[name]
FROM
--Database user
sys.database_principals AS princ
--Login accounts
LEFT JOIN sys.database_principals AS ulogin
ON ulogin.[sid] = princ.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS
perm ON perm.[grantee_principal_id]
= princ.[principal_id]
LEFT JOIN sys.schemas AS permschem ON
permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp
ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G')
-- No need for these system accounts
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 'SQL
User'
WHEN 'U' THEN 'Windows
User'
WHEN 'G' THEN 'Windows
Group'
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE
perm.[class_desc] --
Higher-level objects
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]) -- General
objects
END,
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] =
members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] =
members.[member_principal_id]
--Login accounts
LEFT JOIN sys.database_principals AS ulogin
ON ulogin.[sid] = membprinc.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS
perm ON perm.[grantee_principal_id]
= roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON
permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp
ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN ('S','U','G')
-- No need for these system accounts
AND membprinc.[name] NOT IN ('sys',
'INFORMATION_SCHEMA')
UNION
--3) List all access provisioned to the
public role, which everyone gets by default
SELECT
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[LoginName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN
obj.[type_desc] --
Schema-contained objects
ELSE
perm.[class_desc] --
Higher-level objects
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]) -- General
objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS
perm ON perm.[grantee_principal_id]
= roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id]
= perm.[major_id]
--All objects
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] =
perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp
ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R'
AND roleprinc.[name] = 'public'
AND obj.[is_ms_shipped] = 0
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
order by username;
Enjoy !!
Add a comment