Find and Fix the Orphan Users in SQL Server
Orphaned user in SQL Server, refer to those present at the database level without corresponding logins at sql instance level.
Users without mapped login, typically arise during actions like database restoration often occurring during database migrations.
When the mapping between SQL Server login and database user is missed, then login will fail to access the database.
If you can able to access database regularly and after database restoration you are facing access related issue, Then as part of troubleshooting your first step is to check if any orphan users exits are database level.
Check Orphaned users in SQL Server
As we want to check the orphan user and this exits in database level, we need to navigate to the Database.
So, we use the command “USE DATABASE_NAME” on which we want to check the orphan user.
After that execute EXEC SP_CHANGE_USERS_LOGIN ‘REPORT’
EXEC means execute
SP_CHANGE_USERS_LOGIN is an inbuild stored procedure which help to get the orphan user database
‘REPORT’ is a parameter to get the all the orphan user in the database.
Copy below code and change DATBASE_NAME to the database you want to check on SQL instance.
USE <DATABASE_NAME>
GO
EXEC SP_CHANGE_USERS_LOGIN 'REPORT'
GOBelow is the screenshot showing how to execute the command and example of the orphan user.
At this stage , you are able to identify the Orphan user in SQL Server using the command. As the issue is identified, Let us work on the resolution for the same. To fix the orphan user problem, we can use 3 different ways , I will focus on frequently used process
Ways to Fix Orphan Users:
Type1: Login and Username Exists and same
If login and username are available and same and not mapped to each other, then auto_fix option will fix the orphan user and gives output showing as highlighted in below screenshot.
If login / username missing, create missing loginname or username and follow below process.
Use the command “USE DATABASE_NAME” to navigate to the database ,on which we want to fix the orphan user.
After that execute EXEC sp_change_users_login AUTO_FIX, ‘LoginName/UserName’
EXEC means execute
sp_change_users_login is an inbuild stored procedure which help to get the orphan user details
AUTO_FIX and LoginName are the parameters passed to stored procedure to fix the orphan user if login and username are same.
Copy below code and change DATBASE_NAME to the database you want to fix on SQL instance.
“akeesoft_User” is an example of Login.
“AKEESOFT_TRAINING” is an example of database name on which i want to fix orphan user.
USE <DATABASE_NAME>
sp_change_users_login AUTO_FIX, 'LoginName/UserName'
Go
After executing the above command , orphan user is fixed and report is clean, Which tells us that orphan user issue is fixed and you should be able to connect to the database.
