MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Fix all Orphaned users in selected databases (or all DBs if you want)

leave a comment »


Hello,

I know someone else out there already wrote this procedure but I needed it and I couldn’t find it online so I re-wrote it (and hopefully improved it).

Frankly I wouldn’t use it on a COMPLEX production environment (for obvious security reasons), I wrote it to help me in a migration through 4 DEV environments.

If you think the code can be improved, do not hesitate to post a comment.

Create the following procedure in the master DB:

CREATE PROCEDURE SP_FixOrphans
AS
BEGIN
 SET NOCOUNT ON;
DECLARE @username VARCHAR(30);
CREATE TABLE #tempTable (sUserName Varchar(30), UserSID varchar(200));
INSERT INTO #tempTable (sUserName, UserSID) EXEC sp_change_users_login 'report';
DECLARE db_cursor CURSOR FOR select sUserName from #tempTable;
OPEN db_cursor ;
FETCH NEXT FROM db_cursor INTO @username  ;
WHILE @@FETCH_STATUS = 0  BEGIN   ;
 EXEC sp_change_users_login 'update_one', @username, @username;
FETCH NEXT FROM db_cursor INTO @username  ;
END   ;
CLOSE db_cursor   ;
DEALLOCATE db_cursor  ;
DROP table #tempTable;
END
GO

Then you can execute it on every DB or some of them by executing this:

 
declare @DBroot varchar(20)
declare @DBRootLen varchar(20)
declare @cmd1 varchar(200)
declare @cmd2 varchar(200)
set @DBroot = 'YOURDB' -- Root of databases whose logins you want to fix
set @DBRootLen = len(@DBroot)
set @cmd1 = 'if substring(''?'',1,' + @DBRootLen + ') = ''' + @DBroot + ''' print ''?''';
set @cmd2 = 'if substring(''?'',1,' + @DBRootLen + ') = ''' + @DBroot + ''' BEGIN ' + CHAR(13);
set @cmd2 = @cmd2 + 'USE ? ' + CHAR(13);
set @cmd2 = @cmd2 + 'exec SP_FixOrphans ;' + CHAR(13);
set @cmd2 = @cmd2 + 'END ;' + CHAR(13);
exec sp_MSforeachdb @command1=@cmd1,@command2=@cmd2
 
Good luck.
Advertisements

Written by zantoro

August 18, 2010 at 8:14 am

Posted in SQL Server

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: