Syncing Users and permissions while migration or a DB refresh.

This is on request from some of my friends who often do migration of databases from one server to another or refresh Production databases to QA or Dev. They face a lot of issues with some users not being able to login or not having same permissions as they had earlier and so on.

Here are simple steps to copy or sync logins,  users and their permissions while moving databases.

Please note that these need to be done on the server whose permissions you want to retain. In case of migration, it will be the source server. In case of DB refresh from Prod to QA/Dev, it will be QA/Dev server.

Step 1 – Create a Hexadecimal Function. This is one time work.

 

USE [ADMIN]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_hexadecimal]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))
DROP FUNCTION [dbo].[fn_hexadecimal]
GO
CREATE FUNCTION [dbo].[fn_hexadecimal]
(
— Add the parameters for the function here
@binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
return @charvalue

END
GO

Step 2 – Script out Logins – Please set @path to the folder where you want the result to be saved. Currently its set to ‘D:\’.  If you need to change, just change that part and not change  ‘+Convert’ on wards. It will create a folder with Date year format and will store scripts there. 

SET NOCOUNT ON
GO

DECLARE @cmd VARCHAR(MAX), @scriptfile VARCHAR(4000), @path varchar(max)
DECLARE @script varchar(max), @FS int, @OLEResult int, @FileID int
DECLARE @ErrorAll int, @Error int

set @path = ‘H:\MP1Bak\MSSQL2008\Backups\User\’ + CONVERT(varchar, getdate(),112) + ‘\’

— Create directory
SET @cmd = ‘DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_create_subdir N”’ + REPLACE(@path,””,”””) + ”’ IF @ReturnCode <> 0 RAISERROR(”Error creating directory.”, 16, 1)’
EXECUTE (@cmd)
SET @Error = @@ERROR
IF @Error <> 0 SET @ErrorAll = @Error

SET @scriptfile = @path + CONVERT(VARCHAR(1000),DB_NAME())+’_001_CreateLogins.sql’

set @script = ‘– script to create the required logins
—————————————————————————–
— Created On Server: ‘ + replace(CONVERT(VARCHAR,@@servername),”’\”’,”’$”’) + ‘
— Created On Database: ‘ + CONVERT(VARCHAR,DB_NAME()) + ‘
— Created At: ‘ + CONVERT(VARCHAR,GETDATE(),121) + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ”) + ‘
— Created By: ‘ + SUSER_NAME() + ‘
—————————————————————————–

—————————————————————————–
— Create the windows logins
—————————————————————————–

SELECT @script=coalesce(@script,”,”) + ‘IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ”’ + [name] + ”’)
CREATE LOGIN [‘ + [name] + ‘] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

FROM master.sys.server_principals
where type_desc In (‘WINDOWS_GROUP’, ‘WINDOWS_LOGIN’)
and [name] not in (‘CHDOLENINET\H900218′,’CHDOLENINET\H902377’) — SCOM accounts not required in MEDC
and [name] not in (‘CHDOLENINET\G_SQL-SERVICE-S’) — general exclusions
and [name] not like ‘BUILTIN%’
and [NAME] not like ‘NT AUTHORITY%’
and [name] not like ‘%\SQLServer%’
and sid in ( select sid from ..sysusers where islogin = 1 )

SELECT @script = @script + ‘
—————————————————————————–
— Create the SQL Logins
—————————————————————————–

SELECT @script=coalesce(@script,”,”) + ‘IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ”’ + [name] + ”’)
CREATE LOGIN [‘ + [name] + ‘]
WITH PASSWORD=’ + [Admin].[dbo].[fn_hexadecimal](password_hash) + ‘ HASHED,
SID = ‘ + [Admin].[dbo].[fn_hexadecimal]([sid]) + ‘,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=’ + CASE WHEN is_expiration_checked = 1 THEN ‘ON’ ELSE ‘OFF’ END + ‘, CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ”’ + [name] + ”’)
ALTER LOGIN [‘ + [name] + ‘]
WITH CHECK_EXPIRATION=’ +
CASE WHEN is_expiration_checked = 1 THEN ‘ON’ ELSE ‘OFF’ END + ‘, CHECK_POLICY=’ +
CASE WHEN is_policy_checked = 1 THEN ‘ON’ ELSE ‘OFF’ END + ‘
GO

from master.sys.sql_logins
where type_desc = ‘SQL_LOGIN’
and [name] not in (‘sa’, ‘guest’)
and sid in ( select sid from ..sysusers where islogin = 1 )

SELECT @script = @script + ‘
—————————————————————————–
— Disable any logins
—————————————————————————–

SELECT @script=coalesce(@script,”,”) + ‘ALTER LOGIN [‘ + [name] + ‘] DISABLE
GO

from master.sys.server_principals
where is_disabled = 1
and sid in ( select sid from ..sysusers where islogin = 1 )

SELECT @script = @script + ‘
—————————————————————————–
— Assign groups
—————————————————————————–

select @script=coalesce(@script,”,”) +
‘EXEC master..sp_addsrvrolemember @loginame = N”’ + l.name + ”’, @rolename = N”’ + r.name + ”’
GO

from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in (‘sa’)
AND l.[name] not like ‘BUILTIN%’
and l.[NAME] not like ‘NT AUTHORITY%’
and l.[name] not like ‘%\SQLServer%’
and l.sid in ( select sid from ..sysusers where islogin = 1 )

— print @script

— output to file
— first enable OLE SPs if not done and disable later again

DECLARE @sp_conf TABLE(
[name] [varchar](255) ,
[minimum] [int] NULL,
[maximum] [int] NULL,
[config_value] [int] NULL,
[run_value] [int] NULL
)
DECLARE @OLEstate bit
DECLARE @OLEstate_changed bit

set @OLEstate_changed = 0

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE

INSERT INTO @sp_conf
EXEC sp_configure ‘Ole Automation Procedures’ –, 0

select @OLEstate = config_value from @sp_conf where name = ‘Ole Automation Procedures’

if @OLEstate = 0 — need to set it to 1
begin
EXEC sp_configure ‘Ole Automation Procedures’, 1
RECONFIGURE
set @OLEstate_changed = 1
end

— write file
EXECUTE @OLEResult = sp_OACreate ‘Scripting.FileSystemObject’, @FS OUT
IF @OLEResult <> 0 PRINT ‘Scripting.FileSystemObject’
EXECUTE @OLEResult = sp_OAMethod @FS, ‘DeleteFile’, NULL, @scriptfile
EXECUTE @OLEResult = sp_OAMethod @FS, ‘OpenTextFile’, @FileID OUT, @scriptfile, 8, 1
IF @OLEResult <> 0 PRINT ‘OpenTextFile’
EXECUTE @OLEResult = sp_OAMethod @FileID, ‘WriteLine’, Null, @script
IF @OLEResult <> 0 PRINT ‘WriteLine’
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

— and set back if changed
if @OLEstate_changed = 1
begin
EXEC sp_configure ‘Ole Automation Procedures’, 0
RECONFIGURE
end

EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE

Step 3 – Script out permissions – Please set @path to the folder where you want the result to be saved. Currently its set to ‘D:\’.  If you need to change, just change that part and not change  ‘+Convert’  onwards. It will store scripts in the folder created in Step 02.

SET NOCOUNT ON
DECLARE
@errStatement VARCHAR(MAX),
@msgStatement VARCHAR(MAX),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName VARCHAR(MAX),
@ObjectID [int],
@ObjectName VARCHAR(MAX),
@SQLStatementComplete VARCHAR(MAX),
@scriptfile VARCHAR(4000),
@path varchar(2000),
@FS int, @OLEResult int, @FileID int

DECLARE @UserName sysname
DECLARE @sql VARCHAR(MAX)
DECLARE @temp table (UserName sysname)
DECLARE @ErrorAll int, @Error int, @cmd VARCHAR(MAX)

set @path = ‘H:\MP1Bak\MSSQL2008\Backups\User\’ + CONVERT(varchar, getdate(),112) + ‘\’

— Create directory
SET @cmd = ‘DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_create_subdir N”’ + REPLACE(@path,””,”””) + ”’ IF @ReturnCode <> 0 RAISERROR(”Error creating directory.”, 16, 1)’
EXECUTE (@cmd)
SET @Error = @@ERROR
IF @Error <> 0 SET @ErrorAll = @Error

SET @scriptfile = @path + CONVERT(VARCHAR(1000),DB_NAME())+’_002_CreateUsers.sql’

— intitialisation
SET @SQLStatementComplete = ‘– script to create the required users and permissions
—————————————————————————–
— Created On Server: ‘ + replace(CONVERT(VARCHAR,@@servername),”’\”’,”’$”’) + ‘
— Created On Database: ‘ + CONVERT(VARCHAR,DB_NAME()) + ‘
— Created At: ‘ + CONVERT(VARCHAR,GETDATE(),121) + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ”) + ‘
— Created By: ‘ + SUSER_NAME() + ‘
—————————————————————————–

INSERT @temp
SELECT u.name
FROM sys.sysusers AS u
INNER JOIN [master].[dbo].[syslogins] ON u.[sid] = [master].[dbo].[syslogins].[sid]
WHERE
(u.issqlrole <> 1) AND
(u.isapprole <> 1) AND
(u.[name] not in (‘CHDOLENINET\H900218′,’CHDOLENINET\H902377’)) AND — SCOM accounts not required in MEDC
(u.[name] not in (‘CHDOLENINET\G_SQL-SERVICE-S’)) AND — general exclusions
(u.name <> ‘INFORMATION_SCHEMA’) AND
(u.name <> ‘guest’) AND
(u.name <> ‘sys’) AND
(u.name <> ‘dbo’) AND
(u.name <> ‘system_function_schema’)

WHILE EXISTS (SELECT UserName FROM @temp)
BEGIN
SELECT TOP 1
@UserName = UserName
FROM @temp

SELECT @DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @UserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = ‘User ‘ + @UserName + ‘ does not exist in ‘
+ DB_NAME() + CHAR(13)
+ ‘Please provide the name of a current user in ‘ + DB_NAME()
+ ‘ you wish to script.’
RAISERROR ( @errStatement, 16, 1 )
END
ELSE
BEGIN
/*
SET @msgStatement = ‘–Security creation script for user ‘
+ @ServerUserName + CHAR(13) +
+ ‘–Add User To Database’ + CHAR(13) + ‘USE [‘ + DB_NAME() + ‘]’
+ CHAR(13) + ‘EXEC [sp_grantdbaccess]’ + CHAR(13) + CHAR(9)
+ ‘@loginame = ”’ + @ServerUserName + ”’,’ + CHAR(13) + CHAR(9)
+ ‘@name_in_db = ”’ + @UserName + ”” + CHAR(13)
+ ‘–Add User To Roles’
*/
SET @msgStatement = ‘–Security creation script for user ‘
+ @ServerUserName + CHAR(13) +
+ ‘–Add User To Database’ + CHAR(13) + ‘USE [‘ + DB_NAME() + ‘]’
+ CHAR(13) + ‘CREATE USER [‘ + @ServerUserName + ‘] FOR LOGIN [‘ + @UserName + ‘]’ + CHAR(13)
+ ‘–Add User To Roles’
SET @SQLStatementComplete = @SQLStatementComplete + ‘ ‘ + CHAR(13) + ” + @msgStatement
DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT [name]
FROM [dbo].[sysusers]
WHERE [uid] IN ( SELECT [groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID )
OPEN _sysusers
FETCH NEXT FROM _sysusers INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ‘EXEC [sp_addrolemember]’ + CHAR(13)
+ CHAR(9) + ‘@rolename = ”’ + @RoleName + ”’,’ + CHAR(13)
+ CHAR(9) + ‘@membername = ”’ + @UserName + ””
SET @SQLStatementComplete = @SQLStatementComplete + ‘ ‘ + CHAR(13) + ” + @msgStatement
FETCH NEXT FROM _sysusers INTO @RoleName
END
SET @msgStatement = ‘–Set Object Specific Permissions’
SET @SQLStatementComplete = @SQLStatementComplete + ‘ ‘ + CHAR(13) + ” + @msgStatement
DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT
DISTINCT ( [sysobjects].[id] ),
‘[‘ + USER_NAME([sysobjects].[uid]) + ‘].[‘
+ [sysobjects].[name] + ‘]’
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ”
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 205 )
SET @msgStatement = @msgStatement + ‘SELECT,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 205 )
SET @msgStatement = @msgStatement + ‘INSERT,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 205 )
SET @msgStatement = @msgStatement + ‘UPDATE,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 205 )
SET @msgStatement = @msgStatement + ‘DELETE,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 224
AND [protecttype] = 205 )
SET @msgStatement = @msgStatement + ‘EXECUTE,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 26
AND [protecttype] = 205 )
SET @msgStatement = @msgStatement + ‘REFERENCES,’
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ‘,’
SET @msgStatement = LEFT(@msgStatement,
LEN(@msgStatement) – 1)
SET @msgStatement = ‘GRANT’ + CHAR(13) + CHAR(9)
+ @msgStatement + CHAR(13) + CHAR(9) + ‘ON ‘
+ @ObjectName + CHAR(13) + CHAR(9) + ‘TO ‘
+ ‘[‘ + @UserName + ‘]’
SET @SQLStatementComplete = @SQLStatementComplete + ‘ ‘ + CHAR(13) + ” + @msgStatement
END
SET @msgStatement = ”
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 206 )
SET @msgStatement = @msgStatement + ‘SELECT,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 206 )
SET @msgStatement = @msgStatement + ‘INSERT,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 206 )
SET @msgStatement = @msgStatement + ‘UPDATE,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 206 )
SET @msgStatement = @msgStatement + ‘DELETE,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 224
AND [protecttype] = 206 )
SET @msgStatement = @msgStatement + ‘EXECUTE,’
IF EXISTS ( SELECT *
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 26
AND [protecttype] = 206 )
SET @msgStatement = @msgStatement + ‘REFERENCES,’
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ‘,’
SET @msgStatement = LEFT(@msgStatement,
LEN(@msgStatement) – 1)
SET @msgStatement = ‘DENY’ + CHAR(13) + CHAR(9)
+ @msgStatement + CHAR(13) + CHAR(9) + ‘ON ‘
+ @ObjectName + CHAR(13) + CHAR(9) + ‘TO ‘
+ ‘[‘ + @UserName + ‘]’
SET @SQLStatementComplete = @SQLStatementComplete + ‘ ‘ + CHAR(13) + ” + @msgStatement
END
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
CLOSE _sysusers
DEALLOCATE _sysusers
SET @SQLStatementComplete = @SQLStatementComplete + ‘ ‘ + CHAR(13)
END

DELETE FROM @temp where UserName = @UserName
END

— print @SQLStatementComplete

— output to file
— first enable OLE SPs if not done and disable later again

DECLARE @sp_conf TABLE(
[name] [varchar](255) ,
[minimum] [int] NULL,
[maximum] [int] NULL,
[config_value] [int] NULL,
[run_value] [int] NULL
)
DECLARE @OLEstate bit
DECLARE @OLEstate_changed bit

set @OLEstate_changed = 0

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE

INSERT INTO @sp_conf
EXEC sp_configure ‘Ole Automation Procedures’ –, 0

select @OLEstate = config_value from @sp_conf where name = ‘Ole Automation Procedures’

if @OLEstate = 0 — need to set it to 1
begin
EXEC sp_configure ‘Ole Automation Procedures’, 1
RECONFIGURE
set @OLEstate_changed = 1
end

— write file
EXECUTE @OLEResult = sp_OACreate ‘Scripting.FileSystemObject’, @FS OUT
IF @OLEResult <> 0 PRINT ‘Scripting.FileSystemObject’
EXECUTE @OLEResult = sp_OAMethod @FS, ‘DeleteFile’, NULL, @scriptfile
EXECUTE @OLEResult = sp_OAMethod @FS, ‘OpenTextFile’, @FileID OUT, @scriptfile, 8, 1
IF @OLEResult <> 0 PRINT ‘OpenTextFile’
EXECUTE @OLEResult = sp_OAMethod @FileID, ‘WriteLine’, Null, @SQLStatementComplete
IF @OLEResult <> 0 PRINT ‘WriteLine’
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

— and set back if changed
if @OLEstate_changed = 1
begin
EXEC sp_configure ‘Ole Automation Procedures’, 0
RECONFIGURE
end

EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE

 

Now we have the logins, users and their permissions scripted out. You can go through the normal backup restore process. After the restore run the following steps:

Please note that these need to be done on the destination server where you want to deploy the permissions scripted out earlier.

Step 4 : Drop existing users/permissions, no issue of SIDs and orphan users. Please ignore any errors. This will not drop any schema owners.

 

/*
drop all users

*/

USE XXXXX

set nocount on

IF EXISTS (
SELECT 1
FROM sys.sysusers AS u
WHERE (u.issqlrole <> 1) AND
(u.isapprole <> 1) AND
(u.name <> ‘INFORMATION_SCHEMA’) AND
(u.name <> ‘guest’) AND
(u.name <> ‘sys’) AND
(u.name <> ‘dbo’) AND
(u.name <> ‘system_function_schema’)
)
BEGIN
DECLARE @temp table (UserName sysname)
DECLARE @sql varchar(MAX)
DECLARE @UserName sysname
INSERT @temp
SELECT u.name
FROM sys.sysusers AS u
WHERE (u.issqlrole <> 1) AND
(u.isapprole <> 1) AND
(u.name <> ‘INFORMATION_SCHEMA’) AND
(u.name <> ‘guest’) AND
(u.name <> ‘sys’) AND
(u.name <> ‘dbo’) AND
(u.name <> ‘system_function_schema’)

WHILE EXISTS (SELECT UserName FROM @temp)
BEGIN
SELECT TOP 1 @UserName = UserName FROM @temp
IF NOT EXISTS (
SELECT 1
FROM [sys].[all_objects] AS o
INNER JOIN [sys].[schemas] AS s
ON o.schema_id = s.schema_id
WHERE s.name = @UserName
) AND EXISTS (
SELECT 1
FROM [sys].[schemas] AS s
WHERE s.name = @UserName
)
BEGIN
SET @sql = ‘DROP SCHEMA [‘ + @UserName + ‘]’
PRINT ‘ Deleting schema ‘ + @UserName
EXEC(@sql)
END

SET @sql = ‘DROP USER [‘ + @UserName + ‘]’
PRINT ‘ Deleting user ‘ + @UserName
EXEC(@sql)

DELETE FROM @temp where UserName = @UserName
END
END

Step 5 : Run create login script generated and saved in step 2.

Step 6 : Run create users script generated and saved in step 3. It will also grant permissions.

Step 6 : Find and fix orphaned users if any.

 

–Part 1 – Find orphaned users in current Database
sp_change_users_login @Action=’Report’;
–Part 2 – Fix orphaned users in current Database
DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR
FOR SELECT
‘EXEC sp_change_users_login ”UPDATE_ONE”, ”’ + name
+ ”’, ”’ + name + ””
FROM
sysusers
WHERE
issqluser = 1
AND name NOT IN (‘guest’, ‘dbo’, ‘sys’, ‘INFORMATION_SCHEMA’)

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (
@SQL
)
FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

GO

–Same as Part 1
–You can comment this part out, but it shows you that the orphaned users were actually fixed.
sp_change_users_login @Action=’Report’

Thats it, we have all our users moved along with database in case of migration or we have all the users from QA/Dev with all permissions as they were before DB refresh.

About: Niraj Kumar


Leave a Reply

Your email address will not be published. Required fields are marked *