SQL Server – Orphaned Users

SQL Server – Orphaned Users

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.
A database user can become orphaned if the corresponding SQL Server login is dropped.
Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server.
Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance

Useful Tables and Stored Procedures

Orphaned Users Reports including Windows Logins

Drop Orphaned Users

Drop orphaned users who doesn’t have any dependencies.
If they have dependencies, you need to drop the dependencies explicitly before dropping the orphaned users.
Otherwise , you will encounter errors like this.

/*
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
*/

Leave a Reply

Your email address will not be published. Required fields are marked *

*