Compare two users permissions on SQL Server database?

Question

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.

Answer 1

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

Answer 2

With help of colleague I got answer on my question.
EDIT:
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

Answer 3

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

Answer 4

When comparing logins you can use the following sp

exec sp_helplogins 'loginname'

https://msdn.microsoft.com/en-us/library/ms190304.aspx

for comparing users in the current database you can use

EXEC sp_helpuser 'username'

https://msdn.microsoft.com/en-us/library/ms187732.aspx