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?

marc_s
  • 8,932
  • 6
  • 45
  • 51
Daniel James Bryars
  • 701
  • 1
  • 5
  • 17

6 Answers6

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.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
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

Phú Nguyễn Dương
  • 101
  • 1
  • 2
  • 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 – Phú Nguyễn Dương Jun 25 '18 at 07: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
Tom V
  • 15,670
  • 7
  • 63
  • 86
Devshish
  • 41
  • 1
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
SQLDBAWithABeard
  • 645
  • 1
  • 6
  • 13
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
Michael Green
  • 24,839
  • 13
  • 51
  • 96
shohel ahmed
  • 1
-1
EXEC sp_MSforeachdb 'USE ? exec sp_change_users_login ''Auto_Fix'', ''Username'';'
Gregory Liénard
  • 107
  • 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 '22 at 07:40