It was recently brought to my attention that a post with the script I talk about below could not be found on my blog. Either I thought I blogged about it and never did or it got lost when I had to change hosting providers late last year. My apologies for those who have been looking for it, but let’s rectify that and get down to it.
In my presentation on “How Active Directory Affects SQL Server” I talk about different ways people can gain access to your server. The most common first step is to look at the logins in SQL to verify if someone has access. That’s a great first step and the good news is that these days people have learned to take it a step further and check the groups that have logins as well. It seems like that should do it, but it’s not enough.
Active Directory allows us to nest groups. That means a group can be a member of another group. There are three types of groups in AD and there are rules around which types can be nested in other types. I’ll save that discussion for another day, but if you’re curious I have a diagram in my slide deck from the above presentation. In order to fully verify access to your server, you have to traverse every single group up to the top of the nesting chain. In a large environment it’s time consuming and easy to overlook an account when going back and forth.
The following script will look at the service accounts of all SQL instances on your machine and verify they do not have local administrative access. I’ll put a note here that this default installation behavior was removed in SQL 2012, but that doesn’t mean someone didn’t grant the permission after the fact. I realize this is written in VBScript and I’ll work on converting it to PowerShell, but keep in mind I wrote this in 2007.
Let me know if you have any issues or need a change in functionality and I’ll see what I can do. As always, the use of this script is at your own risk and should be tested in a development environment. I assume no responsibility for your use of the code. You’ll need to rename the extension to .vbs.
