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
- Check out this tips on Auditing and Security from MSSQLTIps.com:
