Fixing Orphaned Users in SQL Server Database

How to find Orphaned Users in SQL Server
Database Administrator can execute the below TSQL script against OrphanedUsers database on SQLServerB Instance to find the list of Orphaned Users.

Use OrphanedUsers
GO
EXEC SP_CHANGE_USERS_LOGIN ‘REPORT’
GO

 

How to troubleshoot Orphaned Users in SQL Server
Database Administrator needs to execute the below mentioned TSQL script to resolve the Orphaned Users issue in SQL Server. However, you need to be a member of db_owner fixed database role to resolve orphaned user issue.

Use OrphanedUsers
GO
EXEC SP_CHANGE_USERS_LOGIN ‘REPORT’
GO

EXEC SP_CHANGE_USERS_LOGIN ‘update_one’ , ‘UserA’ , ‘UserA’
GO

EXEC SP_CHANGE_USERS_LOGIN ‘update_one’ , ‘UserB’ , ‘UserB’
GO

The stored procedure SP_CHANGE_USERS_LOGIN accepts one among the three values like AUTO_FIX, REPORT or UPDATE_ONE for @Action parameter.

AUTO_FIX: If this value is used for @Action parameter, it will create a SQL Server Login if it was not present earlier and will synchronize the SQL Server Login with that of the Database User. You also need to provide a password if the SQL Server Login was not available earlier so that it can be created with a user specific password.

REPORT: If this value is used for @Action parameter, it will display the list of all the Orphaned Users along with the SID (Security Identifiers) value within the current database which are not linked to a SQL Server Login.
UPDATE_ONE: If this value is used for @Action parameter, it will synchronize the specified database user with an existing SQL Server Login.

How to change the Password for a SQL Server Login Account
Once the Orphaned User Issue is resolve and you are able to access the database, then if you want to change the password for the SQL Server Login then it can be done using sp_password system stored procedure. The below TSQL script can be used to change the password for SQL Server Login UserA.

USE master
GO
sp_password @old=’UserA’, @new =’UserA$’, @loginame = ‘UserA’
GO

Conclusion
Orphaned User related issues are common when you are restoring the copy of database from one SQL Server Instance to another SQL Server Instance. In this article you have seen how to identify orphaned users and once identified how they can be resolved. As a Database Administrator you need to make sure that Orphaned Users are not there after the restore of database on another instance of SQL Server. As a Database Administrator you also need to make sure that unwanted database users are dropped appropriately.

]]>

Leave a comment

Your email address will not be published.