Find SQL database Orphaned Users SQL script

27 January,2021 by Tom Collins

Question: Do you have a sql script to list database orphaned users. ? I need to run a daily report , checking if orphaned database users exist and then having a process to work with the application owners to clean up.

 

Answer: A standard script used is below. One of the keys is to differentiate between database users which are orphaned versus database users which require to be remapped. An orphaned sql database user should have an value of "INSTANCE" in the column "authentication_type_desc" in the sys.database_principals, while a sql database user without login should have a value "NONE"

SELECT @@servername as server_name,db_name() as db, dp.type_desc, dp.name AS user_name 
FROM sys.database_principals AS dp  
LEFT JOIN sys.server_principals AS sp  
    ON dp.SID = sp.SID  
WHERE sp.SID IS NULL  
    AND authentication_type_desc = 'INSTANCE';  

Read more on SQL Server database user management

Understanding SQL Server Orphaned Users

Find database users mapped to db_owner role

How to find list of AD groups of a user from SQL Server

Security audit for SQL public role


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


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on Find SQL database Orphaned Users SQL script


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