principlesqlMinor
Compare two users permissions on SQL Server database?
Viewed 0 times
permissionssqlusersdatabasetwoservercompare
Problem
Is it possible to compare permissions of two users on one SQL Server database?
Is there any simple query to do it?
I am asking this because I want to find out if user B has different or same level of permissions as user A on a database.
Is there any simple query to do it?
I am asking this because I want to find out if user B has different or same level of permissions as user A on a database.
Solution
Here are two queries I have used to compare permissions between database users.
The first shows the database roles (if any) to which the database user has membership. The second shows individual GRANT and DENY permissions.
The first shows the database roles (if any) to which the database user has membership. The second shows individual GRANT and DENY permissions.
--Database user and role memberships (if any).
SELECT u.name, r.name
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm
ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals r
ON r.principal_id = rm.role_principal_id
WHERE u.type != 'R'
AND u.[name] = 'DBUserName';
GO
--Individual GRANTs and DENYs.
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission],
sec.class_desc Class, object_name(sec.major_id) [Securable],
sec.major_id [Securible_Id]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE prin.[name] = 'DBUserName'
ORDER BY [User], [Permission];
GOCode Snippets
--Database user and role memberships (if any).
SELECT u.name, r.name
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm
ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals r
ON r.principal_id = rm.role_principal_id
WHERE u.type != 'R'
AND u.[name] = 'DBUserName';
GO
--Individual GRANTs and DENYs.
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission],
sec.class_desc Class, object_name(sec.major_id) [Securable],
sec.major_id [Securible_Id]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE prin.[name] = 'DBUserName'
ORDER BY [User], [Permission];
GOContext
StackExchange Database Administrators Q#152621, answer score: 7
Revisions (0)
No revisions yet.