HiveBrain v1.2.0
Get Started
← Back to all entries
principlesqlMinor

Compare two users permissions on SQL Server database?

Submitted by: @import:stackexchange-dba··
0
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.

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.

--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];
GO

Code 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];
GO

Context

StackExchange Database Administrators Q#152621, answer score: 7

Revisions (0)

No revisions yet.