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