Working with Orphan User in SQL Server

In our day to day work in SQL Server, we have to restore the database frequently. But the problem is the users which are also restored with the bak file and remain unmapped. In that case, we find the login for a user in the server level but failed to access the newly restored database.

This is very much classic problem for restore database in SQL Server. This type of unmapped users known as Orphan User.

We can map or create orphan users very easily using below tSQLs...

1. Find the Orphan Users of a database.

--Check orphaned users USE [TEST_DB] EXEC sp_change_users_login 'Report'; -- OR use below SQL USE [TEST_DB] SELECT dp.type_desc, dp.SID, dp.name AS user_name,* FROM sys.database_principals AS dp where type <> 'R' and authentication_type <> 0 and not exists (select * from sys.server_principals AS sp where  dp.SID = sp.SID)

2. If the Orphan User already exists in the server level login, then you just need to map this user to that login...

-- if login exists and need to map only -- "db_login1" is the user and login name EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

3. If the Orphan User does not exist in the server level login, then we have to create the server login using this user info ... use below SQL ...

-- if login dose not exists and need to create one EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'password'; -- OR user below Create Login command using user SID -- take User SID from below sql SELECT dp.type_desc, dp.SID, dp.name AS user_name,* FROM sys.database_principals AS dp -- Use this SID to create Login CREATE LOGIN db_login_1 WITH PASSWORD = 'use_a_strong_password_here', SID = 0xB171D3B5A352A846847342C5E46620BA; -- Use this for WINDOWS Login CREATE LOGIN db_login_1 FROM WINDOWS;

4. If you face any problem with a user in the master database, use below SQL to fix ...

-- fix master bd ALTER USER <user_name> WITH Login = <login_name>;