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
