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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
With help of colleague I got answer on my question.
Forgot to mention, we had to check first if two or more users have server side permissions over database and than to check did anyone get permissions on database itself.
--filter query declare @filter nvarchar(200); declare @DBLevelPrincipals table( UserName sysname Null ,RoleName sysname Null ,LoginName sysname Null ,DefDBName sysname Null ,DefScName sysname Null ,UID int Null ,SID varbinary(85) Null ); Insert into @DBLevelPrincipals exec sys.sp_helpuser; --database-level principals in the current database --Select * from @DBLevelPrincipals with UsersPermissions as ( -- RESULT SET FOR ALL FIXED SERVER-ROLES select lgn.name collate sql_latin1_general_cp1_ci_as as 'MemberName', SUSER_NAME(rm.role_principal_id) collate sql_latin1_general_cp1_ci_as as 'ServerRole', lgn.sid as 'MemberSID', 'Server level role' as 'RoleLevel' from sys.server_role_members rm join sys.server_principals lgn on rm.member_principal_id = lgn.principal_id where rm.role_principal_id >=3 AND rm.role_principal_id <=10 union --database-level principals in the current database select UserName collate sql_latin1_general_cp1_ci_as, RoleName collate sql_latin1_general_cp1_ci_as, [SID], 'Database level role' from @DBLevelPrincipals ) Select * from UsersPermissions where (@filter is not null and (MemberName = @filter or ServerRole = @filter)) or @filter is null order by MemberName, RoleLevel desc
And if you do not have permission to execute
sp_helplogins you can use this query:
/* Ref: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql */ select s.name as 'Schema' ,o.name as Object ,dp.name as user_group_name ,p.class_desc as class ,p.permission_name as permission ,p.state_desc as state ,case major_id when 0 then state_desc + ' ' + permission_name + ' to [' + dp.name + ']' COLLATE LATIN1_General_CI_AS else state_desc + ' ' + permission_name + ' on ['+ s.name + '].[' + o.name + '] to [' + dp.name + ']' COLLATE LATIN1_General_CI_AS end as [how granted] from sys.database_permissions as p left join sys.all_objects as o on p.major_id = o.object_id left join sys.schemas as s on o.schema_id = s.schema_id left join sys.database_principals as dp on p.grantee_principal_id = dp.principal_id where p.major_id >= 0 -- Exclude system objects order by 1, 2, 3, 4, 5, 6
When comparing logins you can use the following sp
exec sp_helplogins 'loginname'
for comparing users in the current database you can use
EXEC sp_helpuser 'username'