sp_validatelogins

This month blog post I would like to talk about another easily overlooked but very useful tool in SQL Server, the sp_validatelogins stored procedure, which lists out Windows (and Active Directory) users and groups that are mapped to SQL Server logins but no longer exist in the Windows (and Active Directory) environment. Now let's see a demo to show how it works.

In this demo, I created a new Windows user, named peter, in my laptop DESKTOP-LJND0A5. Then I created a SQL Server login DESKTOP-LJND0A5\peter for this Windows user.

Run the sp_validatelogins, you should see no result. 

Now let's remove the Windows user.

Run sp_validatelogins again, now you can see the removed user DESKTOP-LJND0A5\peter in the stored procedure's result.
 

Now you should remove the orphaned SQL Server login. Orphaned login is one of the vulnerabilities that can be used by malicious users to attack your valuable production databases. And that is why some companies avoid adding Windows user directly into SQL Server login, while only allows Windows group to be added into SQL Server.