Understanding SQL Server Orphaned Users

04 February,2021 by Tom Collins

SQL Server Orphaned Users are a typical item on security vulnerability reports.  The next step is to understand why and when SQL orphaned users occur , and thinking about how to set up a process to report and actions to fix.

There are multiple reasons why the SQL Server database Orphaned user can occur

Example 1 : When a database is moved or migrated, the database users in the migrated or moved database are not mapped to a specific SQL Server login on the new SQL Server instance.
Example 2 : A SQL database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a SQL server instance cannot log in to the instance.
Example 3 : The DBA received a request to remove a database user and only deleted the SQL login

Keeping the SQL Server orphaned database users will maintain a security risk because if a SQL login is maliciously mapped to an orphaned database user, the login inherits the orphaned database user’s permissions on the database.

One exception is Contained databases.Contained Databases are isolated from the SQL Server instance and you can log into them by just using their users. This is the  case where you musn't consider a database user as orphaned.

In most DBA management environments - there will be either alerting or a Daily report listing db orphaned users. Use this Find SQL database Orphaned Users SQL script 

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Understanding SQL Server Orphaned Users

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer