Script to find out Orphaned AD/Windows Logins

It is easy to find out the orphaned SQL logins by comparing the SID of SQL Login and User, but what in case of windows login.

Take an example if windows login is dropped and it is still exists in SQL Server. You can find out all the delete windows login which is orphaned in SQL Server, using below procedure.

Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.

CREATE TABLE #dropped_windows_logins
     [sid]  VARBINARY(85),
     [name] SYSNAME

INSERT #dropped_windows_logins
EXEC sys.Sp_validatelogins

FROM   #dropped_windows_logins

DROP TABLE #dropped_windows_logins 

2 thoughts on “Script to find out Orphaned AD/Windows Logins

  1. v.vtkr

    Hi Jugal,

    Most of the time, we always see only SQL logins become orphanswhen compared with windows logins?

    Could you please explain if you have few free minutes?

    Thanks in Advance!

    1. Jugal Shah Post author

      reason is, most of the company follow the best practice, they are not granting the Windows Users direct access to the SQL Sever, they are giving access through Active Directory group, if the user leave the company they will just remove the user from active directory group. There is no change requires on the SQL Side.


Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s