Identify and Resolve Orphaned SQL Server Database Users

In my experience as a SQL Server DBA, I found myself spending a lot of time making sure that our backup/restore strategy was in tip top shape.  This involved spot checking backups and testing them on test servers to ensure that they were valid.  

I'm reminded of a couple old DBA saying such as "Your data is only as good as your last backup." and "Your backup is only as good as your last restore."  Testing that your backups will be useful in an disaster recovery situation is very important and essential for job preservation.

Also, as an operational support DBA for various LOB systems such as PeopleSoft and BizTalk, we would spend some time creating copy only backups and restoring them in test and development environments so the developers have a ‘refresh’ of test or development data.  

A possible by-product of moving databases around to various environments is that sometimes it can result in an orphaned user. 

An orphaned user is a user that has no corresponding SQL Server login.  When you create a login so that it can be authenticated against the SQL Server instance you will also create a user and then associate the two together. 

USE [AdventureWorks2012]

GO

CREATE USER "domain\user1" FOR LOGIN "domain\login1";

GO

If a SQL Server log in is dropped but the user is not then the user is orphaned. 

USE [AdventureWorks2012]

GO

Drop Login "domain\login1";

GO

It is prudent to clean up these orphaned  users from time to time to avoid confusion and for security reasons.  It can be very time consuming and brain numbing to manually try to traverse the database objects in SSMS and identify and delete users.  It would also be very risky because you could incorrectly identify and a user as orphaned and delete it and possibly cause serious problems.

There is a way to detect orphaned users in a database by running a system stored procedure.  The system stored procedure is called sp_change_users and has an option where you provide @Action = ‘Report’ as a parameter.

USE [AdventureWorks2012]

GO;

sp_change_users_login @Action='Report';

GO;

The command will return the following result set:

You can also use the sp_change_user_login stored procedure to then associate the orphaned database user to a SQL Server login that you choose.

You can read more about this system stored procedure at:

https://msdn.microsoft.com/en-us/library/ms174378.aspx

Thanks for reading,  

SQL Canuck