19

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?

0

6 Answers 6

16

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.

1
  • Don't suppose this works for Managed Identities?
    – BVernon
    Dec 11, 2023 at 21:56
10

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

1
  • well, i can explain that like this: 1 fetch each login in syslogins, 2 re-map user(scope is database level ) and login (scope is instance level) for each database Jun 25, 2018 at 7:17
4

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
1

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
0

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
-1
EXEC sp_MSforeachdb 'USE ? exec sp_change_users_login ''Auto_Fix'', ''Username'';'
1
  • 1
    While this does fix the orphaned user Username for all the databases of an instance, it does not answer the question of Is there a shorthand way to Auto_Fix all Orphaned Users in an SQL Server 2008 R2 database?
    – John K. N.
    Apr 11, 2022 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.