Fixing Orphaned Users On A Large Scale

A script that generates a script to fix all orphaned users at once.

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;