Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database?

by Daniel James Bryars   Last Updated June 20, 2018 04:06 AM

It's fairly straight forward to fix up a single orphaned SQL user to a login using:

EXEC sp_change_users_login 'Auto_Fix', 'user'

I could script this, but is there an existing stored procedure which automatically tries to fix up every orphaned user in a given database?



Answers 3


Ted Krueger (@onpnt on twitter) wrote a great script that does this. It adds logins for any user without a login and runs the auto_fix. He even wrote one that includes fixing Windows logins:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users

Of course if you want to test it first (or merely perform an audit) you can comment out the action lines (EXEC) and just print out the results.

Aaron Bertrand
Aaron Bertrand
February 14, 2012 15:49 PM

Below is the simple script that does the job perfectly --

USE DBNAME     ----- change db name for which you waant to fix orphan users issue

GO


declare @name varchar(150)

DECLARE cur CURSOR FOR
    select name from master..syslogins

Open cur

FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_change_users_login 'AUTO_FIX', @name

    FETCH NEXT FROM cur into @name

END

CLOSE cur
DEALLOCATE cur
Devshish
Devshish
March 14, 2018 13:06 PM

base on above script, we can fix all user at an instance by using sp_MSForeachdb like this

declare @name varchar(150)
declare @query nvarchar (500)

DECLARE cur CURSOR FOR
    select name from master..syslogins

Open cur

FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN

set @query='USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN   
exec sp_change_users_login ''Auto_Fix'', '''+ @name +'''
END'

EXEC master..sp_MSForeachdb @query

    FETCH NEXT FROM cur into @name

END

CLOSE cur
DEALLOCATE cur

hope it'll help

Phú Nguyễn Dương
Phú Nguyễn Dương
June 20, 2018 03:10 AM

Related Questions



MSDTC Issue SQL Server 2008 R2

Updated August 23, 2016 08:04 AM


Have Subquery return more than 1 Value

Updated January 16, 2018 16:06 PM