[SQL] Create user with specific SID in SQL Server 2014

I need to create a user on my test server that has the same SID as the same user on the live server. This is due to the fact that we frequently copy databases from the live server to the test server to get fresh data, when this is done the user I have on the test server has no access to the copied databases since the user has a different SID than on the live server.

I think I already have the solution, but I'm a bit afraid of just running it straight up. If I seriously mess up then I'll be in trouble. Sure, not as much trouble as if I had run it on the live server, but still. It's not optimal to screw up the test server either.

USE master
GO

DROP LOGIN myuser
GO

CREATE LOGIN [myuser]
WITH PASSWORD = 'mypassword',
SID = 0xEEEEEEEEEEEEEEEEEEEEEEEEEEEE40EE,
DEFAULT_DATABASE = [MYDB],
DEFAULT_LANGUAGE = [us_english],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = ON
GO

ALTER LOGIN [myuser] ENABLE
GO

USE MYDB
GO

DROP USER myuser
GO

CREATE USER myuser FOR LOGIN myuser
GO

EXEC sp_addrolemember 'db_datareader', 'myuser'
EXEC sp_addrolemember 'db_datawriter', 'myuser'


Is this correct??

Continue reading...