Script to generate database Role membership and also to generate commands to add the users to database roles

During the migration of a database from one SQL Instance to another, we might have to re-create the permissions on the target SQL Instance. This is not required in all the cases, but only in some cases where the user permissions are not synced for some reason. So an easy way to script them out before performing the database refresh is to use the below script to capture the existing setup of database role membership:

-- List Database Roles and Members with Server Login 
SELECT ROL.name AS RoleName 
      ,MEM.name AS MemberName 
      ,MEM.type_desc AS MemberType 
      ,MEM.default_schema_name AS DefaultSchema 
      ,SP.name AS ServerLogin 
FROM sys.database_role_members AS DRM 
     INNER JOIN sys.database_principals AS ROL 
         ON DRM.role_principal_id = ROL.principal_id 
     INNER JOIN sys.database_principals AS MEM 
         ON DRM.member_principal_id = MEM.principal_id 
     INNER JOIN sys.server_principals AS SP 
         ON MEM.[sid] = SP.[sid] 
ORDER BY RoleName 
        ,MemberName;

Now after this is captured, you still have to manually run the below commands for each of the users captured:

sp_addrolemember 'Rolename','UserName'

It takes a lot of time to generate a script manually if there are more than 5 or 10 users. Below is a script to generate those commands as well by leveraging on the above script:

SET QUOTED_IDENTIFIER OFF
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME="##USER_PERMISSIONS")
DROP TABLE ##USER_PERMISSIONS
CREATE TABLE ##USER_PERMISSIONS
(ROLENAME VARCHAR (100),
MEMBERNAME VARCHAR(100))
INSERT INTO ##USER_PERMISSIONS
SELECT ROL.name AS RoleName 
      ,MEM.name AS MemberName 
      FROM sys.database_role_members AS DRM 
     INNER JOIN sys.database_principals AS ROL 
         ON DRM.role_principal_id = ROL.principal_id 
     INNER JOIN sys.database_principals AS MEM 
         ON DRM.member_principal_id = MEM.principal_id 
     INNER JOIN sys.server_principals AS SP 
         ON MEM.[sid] = SP.[sid] 
ORDER BY MemberName, Rolename;
SELECT ("sp_addrolemember '"+ ROLENAME +"','"+MEMBERNAME+"'") AS CMD FROM ##user_permissions
go