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

One common scenario I’ve faced is the need to have a SharePoint database replicated to a disaster recovery site.  Depending on the setup and how the DBAs have provisioned the backup databases, we sometimes face issues where there are orphaned users in the new instance.  Here’s the fix for remapping the orphan database users on the new instance:

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

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

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

Use Master 

Go 

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_fixusers

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'

ELSE

PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'

END

GO

CREATE PROCEDURE dbo.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 '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'

go

Leave a Reply