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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

This would be a great use case for using the dbatools command Repair-DbaDbOrphanUser

First you can identify the Orphaned Users

Get-DbaDbOrphanUser -SqlInstance $sqlinstance

ComputerName : SQL01
InstanceName : MSSQLSERVER
SqlInstance  : SQL01
DatabaseName : SockFactoryApp
User         : SockFactoryApp_User

and then resolve them with

Repair-DbaDbOrphanUser -SqlInstance $sqlinstance

ComputerName : SQL01
InstanceName : MSSQLSERVER
SqlInstance  : SQL01
DatabaseName : SockFactoryApp
User         : SockFactoryApp_User
Status       : Success

Answer 5

To fix orphan users for a single database for all users

BEGIN 

DECLARE @username varchar(25) 

DECLARE fixusers CURSOR 
FOR 

SELECT UserName = name FROM sysusers 
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) 
and suser_sname(sid) is null 
ORDER BY name 

OPEN fixusers 

FETCH NEXT FROM fixusers 
INTO @username 

WHILE @@FETCH_STATUS = 0 
BEGIN 
EXEC sp_change_users_login 'update_one', @username, @username 
FETCH NEXT FROM fixusers 
INTO @username 
END 


CLOSE fixusers 

DEALLOCATE fixusers 

END 

go

Answer 6

EXEC sp_MSforeachdb 'USE ? exec sp_change_users_login ''Auto_Fix'', ''Username'';'