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.

[sourcecode language="sql"]
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
[/sourcecode]