IDENTIFY AND FIX THE ORPHANED USERS IN SQL SERVER

IDENTIFY AND FIX THE ORPHANED USERS IN SQL SERVER:

Orphan user are the one which are present in the database level but their relevant logins not present in the server level.

Orphan users are generated when you take a database backup from one server and restored on another server (Mostly during DB migration).

Basically SQL Server login is mapped to database user and this mapping is not properly defined for SQL Server principals then login will not be successfully for that specific user of database on that specific instance and this user is called orphan user.

To find the orphaned users in SQL Server use below command.

USE

USER DATABASE

EXEC SP_CHANGE_USERS_LOGIN ‘REPORT’

GO

We can fix orphaned users by using different methods.

METHOD 1: USING WITH ORPHANED USER SID

If you find any orphaned users, then create login by using orphaned user SID.

Syntax:

USE

MASTER

CREATE LOGIN [LoginName] WITH PASSWORD = ‘login@123’,

SID = 0xF0C10D1C8EDD1C40A735B07DAD54FFAE

METHOD 2: USING UPDATE_ONE

UPDATE_ONE can be used to change user’s SID with Logins SID.

It can be used to map even if Login name and User name are different (or) same.

Now we can create new login.

CREATE LOGIN [LoginName] WITH PASSWORD = ’login@123'

After creating login, we can fix the orphaned user using UPDATE_ONE.

Syntax:

USE

USER DATABASE

sp_change_users_login UPDATE_ONE, ‘UserName’, ‘LoginName’

GO 

METHOD 3: USING AUTO_FIX

By using AUTO_FIX we can solve orphaned users problem in two ways.

TYPE 1:

AUTO_FIX can be used if Login Name and User Name are same.

Create the login first and then assign Login SID to Orphan User.

Syntax:

CREATE LOGIN [LoginName] WITH PASSWORD = ‘login@123’

After creation of login we can fix orphaned user using below syntax.

Syntax:

USE

USER DATABASE

sp_change_users_login AUTO_FIX, ‘LoginName/UserName’

Go

Note: Here LoginName and UserName should be same.

TYPE 2:

AUTO_FIX can be used even without creating the login.

We can fix orphaned user by using below command.

Syntax:

USE

USER DATABASE

sp_change_users_login AUTO_FIX, ‘UserName’, NULL, ‘login@123’

GO

NOTE:

If orphaned user fixed successfully, we will not get any orphaned user (UserName and SID) when you run the below command as shown in below figure

Syntax:

USE

USER DATABASE

EXEC SP_CHANGE_USERS_LOGIN ‘REPORT’

GO