Wednesday, October 15, 2014

Find all permissions for all users in the database

 

Here is the query that lists all the user permissions, you need to run this on proper database to list the permissions, You can also put a filter on user

 

WITH cteUserPermissions
AS
(
-- Permissions provisioned to a sql user
--
or windows user/group directly
SELECT
[UserName] = CASE DP.[type]
WHEN 'S' THEN DP.[name]
WHEN 'U' THEN L.[name]
END,
[UserType] = CASE DP.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = DP.[name],
[Role] = null,
[PermissionState] = P.[state_desc],
[PermissionName] = P.permission_name,
[ObjectName] = OBJECT_NAME(P.major_id),
[ObjectType] = O.type_desc
FROM
sys.database_principals DP
LEFT JOIN sys.login_token L ON DP.[sid] = L.[sid]
LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]
WHERE DP.[type] in ('S','U')

UNION

-- Permissions provisioned to a sql user or windows user/group
--
through a database or application role
SELECT
[UserName] = CASE M.[type]
WHEN 'S' THEN M.[name]
WHEN 'U' THEN L.[name]
END,
[UserType] = CASE M.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = M.[name],
[Role] = R.[name],
[PermissionState] = P.[state_desc],
[PermissionName] = P.permission_name,
[ObjectName] = OBJECT_NAME(P.major_id),
[ObjectType] = O.type_desc

FROM
sys.database_role_members DRM
JOIN sys.database_principals R ON R.[principal_id] = DRM.[role_principal_id]
JOIN sys.database_principals M ON M.[principal_id] = DRM.[member_principal_id]
LEFT JOIN sys.login_token L ON M.[sid] = L.[sid]
LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = R.[principal_id]
LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]

UNION

-- Permissions provisioned to the public role,
--
which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = DP.[name],
[PermissionState] = P.[state_desc],
[PermissionName] = P.permission_name,
[ObjectName] = OBJECT_NAME(P.major_id),
[ObjectType] = O.type_desc
FROM
sys.database_principals DP
LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
JOIN sys.objects O ON O.[object_id] = P.[major_id]
WHERE
DP.
[type] = 'R' -- only roles
AND DP.[name] = 'public' -- only public role
AND O.is_ms_shipped = 0
)
SELECT *
FROM cteUserPermissions
--WHERE UserName in ('{All Users}', 'myusername')
ORDER BY UserName, ObjectName, PermissionState, PermissionName

0 comments:

Post a Comment