SQL Server: How to Remap Existing Database Users on New Instance

During shifting databases acrossdifferent instances a common problem is ORPHAN USERS and remapping of these orphan database users on new instance. That’s what we were facing in these days, but thanks to Chad Mattox who provided a simple solution.

/*******************************************************

This procedure should be created in the Master database. Thisprocedure takes no parameters. It will remap orphaned users in the currentdatabase to EXISTING logins of the same name. This is usefull in the case a newdatabase is created by restoring a backup to a new database, or by attaching thedatafiles to a new server.

*******************************************************/

UseMaster 

Go 

IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL

BEGIN

DROP PROCEDUREdbo.sp_fixusers

IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL

PRINT ‘<<< FAILEDDROPPING PROCEDURE dbo.sp_fixusers >>>’

ELSE

PRINT ‘<<<DROPPED PROCEDURE dbo.sp_fixusers >>>’

END

GO

CREATE PROCEDUREdbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR

FOR

SELECT UserName =name FROM sysusers

WHERE issqluser =1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers

INTO @username

WHILE @@FETCH_STATUS= 0

BEGIN

IF @username=‘dbo’

BEGIN

EXEC sp_changedbowner ‘sa’

END

ELSE

BEGIN

EXEC sp_change_users_login ‘update_one’, @username,@username

END

FETCH NEXT FROM fixusers

INTO @username

END

CLOSE fixusers

DEALLOCATE fixusers

END

go

IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL

PRINT ‘<<<CREATED PROCEDURE dbo.sp_fixusers >>>’

ELSE

PRINT ‘<<< FAILEDCREATING PROCEDURE dbo.sp_fixusers >>>’

go