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,070 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    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.