You are here

Fixing Orphaned Users On A Large Scale

Fixing one orphaned user is simple.  Fixing tens or even hundreds would be laborious.  Here is a quick script that will generate a script that will fix them all in one fell swoop.  Notice that this script does not make the changes.  You must run the output generated by this script.

Be sure you're connected to the database with the orphaned users. I learned the hard way that this will run without error on Master but it will fix nothing unless the orphaned user is in Master.

USE [DATABASE_NAME]
GO

DECLARE @t TABLE( username varchar(100), UserSID varchar(100), dbname varchar(100) null);

INSERT INTO @t (username, UserSID)
exec sp_change_users_login 'report'

UPDATE @t SET dbname = '?';

SELECT 'EXEC sp_change_users_login ''auto_fix'', ''' + username + '''
GO ' FROM @t;

 

Categories: 
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
13 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer