Orphaned Users

Here’s a quick script to fix orphaned users after a migration …

--OrphanedUsers.sql

-- create temp table
	CREATE TABLE #orphans (oname VARCHAR(100), oSID VARCHAR(100) PRIMARY KEY)
	DECLARE @cmd VARCHAR(MAX), @name VARCHAR(100)

-- populate temp table with orphaned logins
	INSERT #orphans(oname,osid)
	EXEC sp_change_users_login @Action='Report';

-- loop to fix / or else create login with default pw
	WHILE (SELECT COUNT(*) FROM #orphans) > 0
	BEGIN
		SELECT TOP 1 @name = oname FROM #orphans 
		SET @cmd = 'EXEC sp_change_users_login ''Auto_Fix'', ''' + @name + ''', NULL, ''B3r12-3x$098f6'';'
		DELETE FROM #orphans WHERE oname = @name
		EXEC (@cmd)
	END

-- tidy up
	DROP TABLE #orphans