SQL Server – AD Groups vs AD Logins

Let me state my position from the outset: Favoring AD Groups for security in SQL Server over individual AD Logins is a mistake. I don’t care what the “best practice” is. It’s a headache with no discernible advantage.

First and foremost, there is no additional functionality gained by using AD groups rather than AD logins. There is nothing that you can point to and say “see, using AD groups lets you do this whereas AD logins cannot do that”. There’s nothing. So, right from the beginning, you can see that the best you can do is break even. Surprisingly, there is at least one functional loss when using AD Groups, which is mentioned below. In most scenarios, both are equally as capable for regulating access to your SQL instance. So, in my opinion, in most cases, that makes this kind of a “fielder’s choice”, as they say in baseball. But again, no functionality improvements are realized by using AD Group security.

The rest of this post is information gleaned from my work experience at a few different companies, all of which were Microsoft dominant shops that used Mixed Authentication Modes (SQL Server and Windows authentication) including AD Groups and individual AD logins.

One company predominantly used individual AD logins (not AD Groups) for handling security to their SQL instances. Yes, there was a long list of logins in the Security/Logins section, but I was able to create some scripts that effectively gathered information I needed to see someone’s permissions. Similarly, I had a script that effectively displayed all users and permissions on a database. Additionally, I had a script that I could run that would “sync up” one user to another “model” user. So when a request came in saying “I need Harry’s access to mirror Sally’s access”, I could do it quickly. When an employee was “going to pursue other interests” (quit or got fired) and that employee’s account was being terminated, I could quickly query the environment and eliminate their access. Whether adding or deleting access, it involved one person (me) and was resolved in one single effort. No additional requests were required, no other departments needed to be involved, no tickets “on hold”, and no follow ups required. Easy.

Another company I worked for fell into the “AD Groups are best” philosophy and tried to handle security with AD Groups. After a few years, they decided that there was a lot of rigmarole in maintaining this choice and we started getting sloppy with granularly controlling access, so therefore decided to switch back to individual AD logins.

A third company that I worked for was switching to predominantly AD Groups and what ended up happening was that for every new database, within every environment, three to four AD groups had to be created. It needed a group for: read, write, execute, and owner, although we would forego the owner group on Prod instances. The logic there is that anyone who should have that much control in a Prod environment is probably a DBA with sysadmin rights anyway. So, in a typical situation, there would be a Dev, Test, and Prod version of any database, requiring a minimum of 11 AD groups per database. Many of them sat empty, but if we didn’t create them all together then we would have a ridiculous number of requests to the System Admins to create AD Groups. Either way would suck.

So, my main problem with using AD Groups is that, in a typical corporate scenario, the DBA’s are transferring their control of security to another department who controls the Active Directory. Many would argue that this separation of powers is inconvenient but beneficial for security, but I call that nonsense. Even if a company policy is to use this approach, as a DBA with a sysadmin role, I am still free to grant anyone access to any instance. It doesn’t prevent any wrongdoing, it just adds annoyance to right-doing. As such, nothing is improved. There’s a feeling like “oh, we have implemented a security process of such and such”. It sounds important. It isn’t. Nothing was gained. All you have done is taken a 30 second task and multiplied it by 10 or more. Now you have to involve other departments to make a Group, probably requiring a formal request which needs to be watched and followed up on. Then, once completed, the DBA must return to the issue and add the AD Group to the SQL instance and grant the proper rights, and then verify that the required users are in fact members of the newly created AD Group. If forgotten, they must return to the System Admins to correct it. The System Admins could forget to implement it or I could forget to request it. Both have happened, in my experience. Once this drawn-out process is completed and everything works as expected, nothing is gained when compared to simply granting access to the individual’s AD login.

Now, let’s talk about granularity of access. What happens if a user has read access to a database and then needs “execute” on one object? You are forced to either make a one-off AD Group with execute permissions on that object or add the AD login to the instance and grant it to their individual account. So what will you do? Does it matter? No matter which you decide, neither option outperforms the other. They are the same, so why do the one that is difficult?

Now here’s the real irony: SQL Server databases already have roles. Roles are groups but they are contained in the database. They are meant to give the ability to collapse multiple similar permission requirements into one manageable object. What’s crazy is to go to another department so they can create an AD Group so that you can put it on your SQL instance and then grant that AD Group group rights. The outcome is a convoluted picture of access rights. After doing this, when you want to peer into someone’s access points, you need to make a PowerShell script to gather all the data because you have to compare dsquery output with T-SQL output. You have gained nothing and you have made your life more difficult. And with many steps mistakes abound (this sounds very Biblical).

Finally, there can be noticeable disadvantages to using AD Groups.

When a user is added to an AD group, it requires them to log off and back on before the new group permissions are usable. There is an alternative way to handle this but will only work if the user has elevated permissions on their computer. Depending on your company’s policies, this alternative may or may not be an option. Also, if this step is overlooked it can make for some confusing troubleshooting and a lot of wasted time. This would not be a concern if there were benefits to speak of, but again, there is no functionality gained when using AD Groups.

Additionally, you cannot set a default schema for a SQL database user based on the AD Group.

So what should be done? Should AD Groups be used? Yes, definitely! Any company will have departments and there should be AD Groups that exist to define and contain users in that department. If a standard set of database permissions always need to be applied to everyone in that group, then by all means, use AD Groups. All other access required on a database should be done at an individual level. AD Groups should be relatively static objects, not changing every time a permission request comes through. They should be the foundation of basic access in your environment but should not be the standard way of granting all access in your environment.