I’m a big proponent of constant learning – looking to learn something new every day. It doesn’t have to be some big revelation. Sometimes it is just a tip on how to do something more efficiently (like in PowerShell) and sometimes it isn’t even new!
Recently, I was in a discussion about proposing a best practice recommendation in the CIS SQL Benchmarks to ensure deleted Active Directory Windows logins are also removed from all SQL Server instances where they were granted a login. One of the team members did a little research and found a reference to a system stored procedure which might help – sys.sp_validatelogins. My first question was – is it a documented procedure? Microsoft warns against using undocumented commands as they could get changed with most any update. Yes, it is officially documented in BOL (2008+). Second question – since which SQL Server version? Since at least SQL 2000 per this BOL! I have to confess, as many years and versions that I’ve been working with SQL Server and researching various security aspects, I was surprised that I didn’t recall this procedure – especially when the first non-BOL reference that I found in my own search turned up an article written by my friend Tim Ford for MSSQLTips!
In large, complex environments, both the processes (coordination between teams with varying responsibilities) and the technical aspects (how to identify these logins) can be, well, complex! But, depending upon your AD structure and the trusts in place, you as a DBA could periodically run this system stored procedure on your instances to find Windows logins or groups which are SQL Server logins but no longer exist in AD. You can then do a more thorough search of the specific instance’s databases and remove the login from all databases where it is a user, ensure that it isn’t a database owner, and ultimately remove the login from the instance. There is no currently known security risk to leave these orphaned logins on your SQL Server, but just like cleaning up orphaned users in your databases which do not have a specific instance login, it is considered a best practice to perform this task. And, for those of us who are neat freaks – it just makes your instance “cleaner” to get rid of the clutter of obsolete logins!
While it would be tempting to automate this check and just drop the database users, then drop the login, I did find that Thomas LaRock documented an anomaly he found several years ago which would make me always want to manually double-check the AD for any accounts reported as orphaned to ensure the account is really no longer valid. But at least you have narrowed down the search by using this procedure.
So, when looking to learn something new – don’t forget sometimes what you may learn is old – both Tim’s and Tom’s blogs were from 2009!