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
Advertisements

Posted on November 3, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: