SQL Script to fix Orphan users in SQL Server database

This blog post explains Orphan user and provides a script to fix Orphan users in SQL Server database.

The basic process to create a database user in the SQL Server is as below.

• Create a login in the master database

• Create a user from the login in the required user database

Once we create a user in a database, it is linked with the master database login using SIDs The SID mapping is required for any login to connect with the database.

We can query the sys.syslogins and sysusers for checking the SID for a user. For example, let’s check the SID for a user [TestLogin] using the following queries.

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestLogin'
GO
USE AdventureWorks2019
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestLogin'
GO

As shown below, the SID returned by both queries is the same.

Orphaned users are present in SQL Server when a user does not have a mapping SID in the master database.

If we move a database to another SQL Server, the SIDs might differ for the logins. Therefore, users do not have a valid mapping with master database SIDs. SQL Server marks these users as Orphan users.

It is essential to fix these Orphan users before we can connect to the database using Orphan users. We can use the stored procedure sp_change_users_login as shown below to get a list of orphan users in the database.

Use <DBName>
Go
sp_change_users_login @Action='Report'
GO

The above query returns the list of orphan users in the database. We need to link the SID of the user (from sysusers) to the master database login (sys.syslogins).

Let’s look at how to fix these orphan users in the SQL Server database.

USE <DBName>
GO

sp_change_users_login @Action='update_one', 
@UserNamePattern=’<username>’, 
@LoginName='<loginname>'
GO

Or we can use the auto_fix parameter to map the orphan login SIDs.

EXEC sp_change_users_login 'Auto_Fix', ‘user>’
GO

Script to automatically fix Orphan users in SQL Server database

The following script automatically detects the orphan user and fixes them using the auto_fix parameter.

declare @name sysname
declare rs cursor for 
       select name from sysusers where issqluser = 1 open rs fetch next from rs into @name while @@fetch_status = 0
       begin
             exec sp_change_users_login 'auto_fix',@name
             fetch next from rs into @name
       end
close rs
deallocate rs