Find all permissions for all users in the database

Post a Comment

 

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

Related Posts

There is no other posts in this category.

Post a Comment

Subscribe Our Newsletter