The other day I was asked to restore a production database into a development environment and then check the database permissions. So after restoring the database I ran

use 'database name' 
exec sp_change_users_login 'report'

to see if there were any orphaned users. This came back clean so I informed the client. It turned out that they wanted the restored databases users/roles configuration to mirror that of a completely different database on the server. So I knocked up a quick query to enumerate users and their role memberships:

use 'database name'
select 
p.name as 'User Name'
,SUSER_SNAME(p.sid) as 'Login'
,(select innerp.name from sys.database_principals innerp 
where innerp.principal_id = r.role_principal_id) 
as 'Role Membership'
from sys.database_principals p
inner join sys.database_role_members r
on p.principal_id = r.member_principal_id

The quirk of using the sys.database_principals catalog view is that roles are also users, that’s why I had to use a correlated sub query to associate users and roles.

So far so good, but with a couple of temporary table variables and the set operator EXCEPT we can also get SQL Server to compare the query results for the two databases. Replace testdb1 and testdb2 with in this query with the appropriate database names and run the whole query. Edit the selects at the end to reconfigure the output to suit.

declare @db1 table
([User] sysname,[Login] sysname, [Role] sysname);
declare @db2 table
([User] sysname,[Login] sysname, [Role] sysname);
use testdb1;
insert into @db1
select 
p.name as 'User Name'
,SUSER_SNAME(p.sid) as 'Login'
,(select innerp.name from sys.database_principals innerp 
where innerp.principal_id = r.role_principal_id) 
as 'Role Membership'
from sys.database_principals p
inner join sys.database_role_members r
on p.principal_id = r.member_principal_id;
use testdb2;
insert into @db2
select 
p.name as 'User Name'
,SUSER_SNAME(p.sid) as 'Login'
,(select innerp.name from sys.database_principals innerp 
where innerp.principal_id = r.role_principal_id) 
as 'Role Membership'
from sys.database_principals p
inner join sys.database_role_members r
on p.principal_id = r.member_principal_id;
select 'These users (and roles) exist in testdb1 but not testdb2';
select * from @db1
except
select * from @db2;
select 'These users (and roles) exist in testdb2 but not testdb1';
select * from @db2
except
select * from @db1;

This query will also pick up if there is the same user in both databases but assigned to different roles.

However it wont pick up any explicit denys or grants at object level. This query will pick those up on a per database basis.

USE testdb1
GO
select so.name as 'Object Name'
,dpp.name
,dp.state_desc
, so.type_desc as 'Object Type' 
from sys.database_permissions dp
join sys.objects so
on so.[object_id] = dp.major_id
JOIN sys.database_principals dpp
on  dp.grantee_principal_id = dpp.principal_id
where dp.state_desc  'GRANT' --Change this as required
GO

It would be a fairly straight forward exercise to put this query into a similar framework to the comparison query above if required.