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.

Sp_validatelogins
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

SELECT *
FROM   #dropped_windows_logins

DROP TABLE #dropped_windows_logins 
Advertisements

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!

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

      Reply

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

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s