Genrate Database User Script with access rights

From last some time I am getting regular reuirment of DB restore from PROD TO UAT or DEV environment. All server having different domain & different user ids in databse.

I have right small script for the same to get user creation script with access rights in one go.

SET NOCOUNT ON

GO

CREATE TABLE #DBROLE (DBNAME VARCHAR(100), DBROLE VARCHAR (100), DBROLE_MEMBER VARCHAR(100),

SID VARBINARY(85), DEFAULT_SCHEMA_NAME VARCHAR(100), LOGIN_NAME VARCHAR(100), DB_PRINCIPAL_ID INT)

INSERT INTO #DBROLE

SELECT DB_NAME() DBNAME, C.NAME, B.NAME, B.SID, B.DEFAULT_SCHEMA_NAME, D.NAME LOGINNAME, B.PRINCIPAL_ID AS LOGIN_NAME

FROM SYS.DATABASE_ROLE_MEMBERS A

INNER JOIN SYS.DATABASE_PRINCIPALS B ON A.MEMBER_PRINCIPAL_ID = B.PRINCIPAL_ID

INNER JOIN SYS.DATABASE_PRINCIPALS C ON A.ROLE_PRINCIPAL_ID = C.PRINCIPAL_ID

LEFT JOIN SYS.SERVER_PRINCIPALS D ON B.SID = D.SID where B.NAME <> ‘dbo’

ALTER TABLE #DBROLE ADD ID INT IDENTITY(1,1)

DECLARE @COUNTER INT, @MAXID2 INT, @LOGIN_DBROLE VARCHAR(MAX)

SELECT @MAXID2 = MAX(ID) FROM #DBROLE

SET @COUNTER = 1

WHILE @COUNTER <= @MAXID2

BEGIN

SELECT @LOGIN_DBROLE = ‘USE [‘+DBNAME+‘]

GO

IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ”’+DBROLE_MEMBER+”’)

BEGIN

CREATE USER [‘+DBROLE_MEMBER+‘]

FOR LOGIN [‘+LOGIN_NAME+‘]’+ISNULL(‘ WITH DEFAULT_SCHEMA=[‘+DEFAULT_SCHEMA_NAME+‘]’,”)+’

END

ALTER USER [‘+DBROLE_MEMBER+‘] WITH LOGIN = [‘+LOGIN_NAME+‘]

EXEC SP_ADDROLEMEMBER ”’+DBROLE+”’,”’+DBROLE_MEMBER+”’

GO

‘ FROM #DBROLE WHERE ID = @COUNTER

SELECT @COUNTER = @COUNTER + 1

PRINT @LOGIN_DBROLE

END

DROP TABLE #DBROLE