Share via

Get mismatch users and roles from two databases

Ankita Sinha 1 Reputation point
2022-03-29T19:22:10.477+00:00

Hi,

I need to mismatched users and roles from two databases in SQL server.

Could anyone help me this ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2022-03-31T21:14:33.213+00:00

    It occurred to me that I have a script at work do quite something like this. And more. Because I guess you also want to copy permissions for these users?

    The below script copy users, roles, role membership and permission from your old database to your new database. It will also drop users, roles, role membership and permissions not in the source database.

    Because the script could do more than you want, I have commented out all execution of the dynamic SQL, so when you run it, you will only see the statements. Then you can decide which you want to run and which you do not.

    The script requires SQL 2017 to run. 

    USE abt_ht_new
go
-- Users.
SELECT @sql = string_agg('DROP USER ' + quotename(name), @nl)
FROM   (SELECT name
        FROM   sys.database_principals
        WHERE  type IN ('U', 'S', 'G')
        EXCEPT
        SELECT name
        FROM   abt_ht_new.sys.database_principals
        WHERE  type IN ('U', 'S', 'G')) AS e
PRINT @sql
--EXEC(@sql)

-- Role membership.  (Always ignore certificate users.)
SELECT @sql = string_agg('ALTER ROLE ' + quotename(rolename) + ' DROP MEMBER ' + quotename(username), @nl)
FROM   (SELECT r.name AS rolename, u.name AS username
        FROM   sys.database_principals r
        JOIN   sys.database_role_members rm ON rm.role_principal_id = r.principal_id
        JOIN   sys.database_principals u ON rm.member_principal_id = u.principal_id
        WHERE  u.type <> 'C'
        EXCEPT
        SELECT r.name AS rolename, u.name AS username
        FROM   abt_ht_new.sys.database_principals r
        JOIN   abt_ht_new.sys.database_role_members rm ON rm.role_principal_id = r.principal_id
        JOIN   abt_ht_new.sys.database_principals u ON rm.member_principal_id = u.principal_id
        WHERE  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)

-- Roles.
SELECT @sql = string_agg('DROP ROLE ' + quotename(name), @nl)
FROM   (SELECT name
        FROM   sys.database_principals
        WHERE  type = 'R'
        EXCEPT
        SELECT name
        FROM   abt_ht_new.sys.database_principals
        WHERE  type = 'R') AS e
PRINT @sql
--EXEC(@sql)

-- Permissions on object level.
SELECT @sql = string_agg('REVOKE ' + permission_name COLLATE database_default + ' ON ' +
                          quotename(schemaname) + '.' + quotename(objname) +
                          ' FROM ' + quotename(username), @nl)
FROM   (SELECT dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
        FROM   sys.database_permissions dp
        JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   sys.objects o ON dp.major_id = o.object_id
        JOIN   sys.schemas s ON s.schema_id = o.schema_id
        WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
          AND  u.type <> 'C'
        EXCEPT
        SELECT dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
        FROM   abt_ht_new.sys.database_permissions dp
        JOIN   abt_ht_new.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   abt_ht_new.sys.objects o ON dp.major_id = o.object_id
        JOIN   abt_ht_new.sys.schemas s ON s.schema_id = o.schema_id
        WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
          AND  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)

-- Permissions on schema level...
SELECT @sql = string_agg('REVOKE ' + permission_name COLLATE database_default + ' ON SCHEMA::' +
                          quotename(schemaname) + ' FROM ' + quotename(username), @nl)
FROM   (SELECT dp.permission_name, s.name AS schemaname, u.name AS username
        FROM   sys.database_permissions dp
        JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   sys.schemas s ON dp.major_id = s.schema_id
        WHERE  dp.class_desc = 'SCHEMA'
          AND  u.type <> 'C'
        EXCEPT
        SELECT dp.permission_name, s.name AS schemaname, u.name AS username
        FROM   abt_ht_new.sys.database_permissions dp
        JOIN   abt_ht_new.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   abt_ht_new.sys.schemas s ON dp.major_id = s.schema_id
        WHERE  dp.class_desc = 'SCHEMA'
          AND  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)

-- And database permissions.
SELECT @sql = string_agg('REVOKE ' + permission_name COLLATE database_default +
                         ' FROM ' + quotename(username), @nl)
FROM   (SELECT dp.permission_name, u.name AS username
        FROM   sys.database_permissions dp
        JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        WHERE  dp.class_desc = 'DATABASE'
          AND  u.type <> 'C'
        EXCEPT
        SELECT dp.permission_name, u.name AS username
        FROM   abt_ht_new.sys.database_permissions dp
        JOIN   abt_ht_new.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        WHERE  dp.class_desc = 'DATABASE'
          AND  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)

-------------------------------------- Grant permissionss only in the old dev/test database. ---------------------------
-- Users.
SELECT @sql = string_agg('CREATE USER ' + quotename(name) + without, @nl)
FROM   (SELECT name, IIF (type = 'S' and suser_sname(sid) IS NULL, ' WITHOUT LOGIN', '') AS without
        FROM   abt_ht_new.sys.database_principals
        WHERE  type IN ('U', 'S', 'G')
        EXCEPT
        SELECT name, IIF (type = 'S' and suser_sname(sid) IS NULL, ' WITHOUT LOGIN', '') AS without
        FROM   sys.database_principals
        WHERE  type IN ('U', 'S', 'G')) AS e
PRINT @sql
--EXEC(@sql)

-- Roles
SELECT @sql = string_agg('CREATE ROLE ' + quotename(name), @nl)
FROM   (SELECT name
        FROM   abt_ht_new.sys.database_principals
        WHERE  type = 'R'
        EXCEPT
        SELECT name
        FROM   sys.database_principals
        WHERE  type = 'R') AS e
PRINT @sql
--EXEC(@sql)

-- Role memmbership..
SELECT @sql = string_agg('ALTER ROLE ' + quotename(rolename) + ' ADD MEMBER ' + quotename(username), @nl)
FROM   (SELECT r.name AS rolename, u.name AS username
        FROM   abt_ht_new.sys.database_principals r
        JOIN   abt_ht_new.sys.database_role_members rm ON rm.role_principal_id = r.principal_id
        JOIN   abt_ht_new.sys.database_principals u ON rm.member_principal_id = u.principal_id
        WHERE  u.type <> 'C'
        EXCEPT
        SELECT r.name AS rolename, u.name AS username
        FROM   sys.database_principals r
        JOIN   sys.database_role_members rm ON rm.role_principal_id = r.principal_id
        JOIN   sys.database_principals u ON rm.member_principal_id = u.principal_id
        WHERE  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)

-- Object level permissions.
SELECT @sql = string_agg(state_desc + ' ' + permission_name COLLATE database_default + ' ON ' +
                          quotename(schemaname) + '.' + quotename(objname) +
                          ' TO ' + quotename(username), @nl)
FROM   (SELECT dp.state_desc, dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
        FROM   abt_ht_new.sys.database_permissions dp
        JOIN   abt_ht_new.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   abt_ht_new.sys.objects o ON dp.major_id = o.object_id
        JOIN   abt_ht_new.sys.schemas s ON s.schema_id = o.schema_id
        WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
          AND  u.type <> 'C'
          AND  EXISTS (SELECT *
                       FROM   sys.objects o2
                       JOIN   sys.schemas s2 ON s2.schema_id = o2.schema_id
                       WHERE  s2.name = s.name
                         AND  o2.name = o.name)
        EXCEPT
        SELECT dp.state_desc,dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
        FROM   sys.database_permissions dp
        JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   sys.objects o ON dp.major_id = o.object_id
        JOIN   sys.schemas s ON s.schema_id = o.schema_id
        WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
          AND  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)

-- On schema level.
SELECT @sql = string_agg(state_desc + ' ' + permission_name COLLATE database_default + ' ON SCHEMA::' +
                          quotename(schemaname) + ' TO ' + quotename(username), @nl)
FROM   (SELECT dp.state_desc, dp.permission_name, s.name AS schemaname, u.name AS username
        FROM   abt_ht_new.sys.database_permissions dp
        JOIN   abt_ht_new.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   abt_ht_new.sys.schemas s ON dp.major_id = s.schema_id
        WHERE  dp.class_desc = 'SCHEMA'
          AND  u.type <> 'C'
          AND  EXISTS (SELECT *
                       FROM   sys.schemas s2
                       WHERE  s2.name = s.name)
        EXCEPT
        SELECT dp.state_desc, dp.permission_name, s.name AS schemaname, u.name AS username
        FROM   sys.database_permissions dp
        JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        JOIN   sys.schemas s ON dp.major_id = s.schema_id
        WHERE  dp.class_desc = 'SCHEMA'
          AND  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)


-- On database...
SELECT @sql = string_agg(state_desc + ' ' + permission_name COLLATE database_default +
                          ' TO ' + quotename(username), @nl)
FROM   (SELECT dp.state_desc, dp.permission_name, u.name AS username
        FROM   abt_ht_new.sys.database_permissions dp
        JOIN   abt_ht_new.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        WHERE  dp.class_desc = 'DATABASE'
          AND  u.type <> 'C'
        EXCEPT
        SELECT dp.state_desc, dp.permission_name, u.name AS username
        FROM   sys.database_permissions dp
        JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
        WHERE  dp.class_desc = 'DATABASE'
          AND  u.type <> 'C') AS e
PRINT @sql
--EXEC(@sql)
    1 person found this answer helpful.
  2. Bert Zhou-msft 3,421 Reputation points
    2022-03-30T05:48:39.673+00:00

    Hi，@Ankita Sinha

    Welcome to Microsoft T-SQL Q&A Forum!
    According to your needs, you should use a query to enumerate users and role members.
    The following query I use is the sys.database_principals catalog view, please try to execute it, pay attention to replace your database name. 

    declare @db1 table  
	([User] sysname,[Login] sysname, [Role] sysname);  
  
declare @db2 table  
	([User] sysname,[Login] sysname, [Role] sysname);  
  
use testquery;  
  
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 finaltest;  
  
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;

    Here is the link you can refer to.

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.