Updated: Dec 31, 2021
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