Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins


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

Problem

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?

Solution

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:

CREATE LOGIN [SAURON\SQLAgentMan] FROM WINDOWS WITH DEFAULT_DATABASE=[master]; 
CREATE LOGIN [SAURON\Thunderbacon] FROM WINDOWS WITH DEFAULT_DATABASE=[master];

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


Last Update:






About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, August 31, 2010 - 5:58:32 PM - Juanita Back To Top
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

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

Old habits die hard Sankar.  Thanks for noticing though.


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

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

Tim,

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

Transact-SQL

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

Two-part names is the standard-compliant behavior.


Learn more about SQL Server tools