Find SQL database Orphaned Users SQL script

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