Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins

By:   |   Comments (5)   |   Related: > Auditing and Compliance


It's audit time and I'm being asked to verify all the Windows-based logins on my Microsoft SQL Server instances. How do I go about doing this? We have a dedicated Security Team that manages all the Windows logins for our domain and their work only crosses with ours when I, as the DBA, request that an Active Directory (AD) group is created for use as a SQL Server principal on one of my servers. We have cases where individual AD accounts have been created as SQL principals (logins) and when staff transition in and out of positions (or employment within our organization for that matter) I am not made aware of the changes so I know we have Windows-based logins on our SQL Server instances that have no corresponding AD login in our domain. Granted this is not a security risk, because of the disconnect, but it is still an audit point and will need to be resolved. What can I do?


There are really three kinds of SQL Server principals you're going to need to deal with in your audit:

The first two items in the list are actually the more difficult items to troubleshoot. What you're asking for, in fact, is the low-hanging fruit. SQL Server logins may sit idle for a long period of time without use depending upon how frequently a database is accessed. Therefore simply auditing for successful logins on you SQL instance will not resolve whether a login (SQL or Windows) is used or not. Windows-based groups are a great method (preferred, actually) for granting access to your SQL Server databases in that the membership in those groups are directly managed by the organization's security administrators typically. The main responsibility of these individuals is to grant and revoke access at the domain level. They should be notified in writing when staff are transitioning in and out of job responsibilities and grant and revoke membership in associated AD groups accordingly. These changes are then passed-through to the SQL Server instances seamlessly. The DBA never is involved. The membership in any login associated with a Windows (AD) group can be returned from within SQL Server using the xp_logininfo extended stored procedure (see the Next Steps section for the previously-published tip associated with this process.)

To answer your question however, I only need to point you to a system stored procedure that will return any Windows-based security principals (individual logins or AD groups) that exist on your SQL instance, but have no corresponding presence at the domain level. May I introduce sp_validatelogins? Readers meet sp_validatelogins, sp_validatelogins meet readers!

The process by which sp_validatelogins is called is as simple as calling any other stored procedure. There are no parameters so simply using the following command will succeed in returning any Windows-based principals that no longer exist in the domain:

EXEC [sys].[sp_validatelogins]

Now let's see this in action. Using my development laptop I've created two new users (simulating the domain environment of an average Windows network):


I'll now go into my SQL instance and run the following code, creating the applicable logins and presenting the security principals:


SELECT [sys].[syslogins].[name]
FROM sys.[syslogins]
WHERE [sys].[syslogins].[isntname] = 1
ORDER BY [sys].[syslogins].[name]


Now I'll proceed to remove the Thunderbacon and SQLAgentMan users from the "domain". Afterwards, if I execute the sys.sp_validatelogins stored procedure I get results that I expect:

EXEC [sys].[sp_validatelogins]


Once the logins are identified you can go through the process of removing any database users associated with the logins and then drop the login from the SQL instance. Good luck on that audit!

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, August 31, 2010 - 5:58:32 PM - Juanita Back To Top (10102)
Thank you !! This is great. Ran immediately once I saw this and I found a login no longer valid in our AD !!!

Friday, October 23, 2009 - 9:57:38 AM - timmer26 Back To Top (4288)

That works as well, but it is so nice being able to use a single, existing, system stored procedure if you have one that does the trick (in my humble opinion.)

Thanks for commenting!

Friday, October 23, 2009 - 9:56:17 AM - timmer26 Back To Top (4287)

Old habits die hard Sankar.  Thanks for noticing though.

Thursday, October 22, 2009 - 12:08:52 PM - --cranfield Back To Top (4281)

I use this query to identify orphaned Windows users i.e. those which dont have a corresponding Windows login:

 --get Windows user orphans

EXEC master..sp_msforeachdb 'use [?] select db_name(), name from [?]..sysusers

where sid not in(select sid from master..syslogins)

and (isntuser = 1

or isntgroup = 1)'

Thursday, October 22, 2009 - 4:07:14 AM - SankarReddy Back To Top (4277)


Good one. Reminds me to do some house cleaning and can use this one. My only comment is why are you using three part notation in the select statements? Its deprecated in SQL Server 2008 and its better to discontinue.

Deprecated Database Engine Features in SQL Server 2008


Three-part and four-part column references in SELECT list

Two-part names is the standard-compliant behavior.

get free sql tips
agree to terms