If your DBA is still adding Windows logins to your database servers then they are doing it wrong.

Using logins (versus Windows groups) is an inefficient method that hasn’t been considered a favorable practice for almost ten years. Oh…sure…it works. I won’t argue that point. But if you are administering hundreds of database servers and you are using logins instead of groups then you are making your environment more complex than it needs to be. When shops exploded with exponential growth in the past decade Microsoft recognized this by publishing guidelines around security best practices that reflect the preference for using groups. Here’s one from 2005 that states simply at the top:

“Because it is inefficient to maintain user accounts directly, assigning permissions on a user basis should be the exception.”

Exception. As in “not the norm”. And say what you want about SQL 2000, but even they knew that using groups was the way to a simpler life.

Here are three reasons why you should be using Windows groups.

Audit compliance

Since those best practices guidelines were published there has been an ever tightening of a grip on systems with regards to security and audits. Most companies that have to be in compliance with government legislation of some type will prefer to use Windows groups to access there database servers. Why? Three words: separation of duties.

The DBA should not be the person that is adding or removing individuals from groups within Active Directory. Those actions should be handled by a security team, and only after being given an approval by the IT custodian (or manager) responsible for the application (also, not the DBA). In other words, security in your company should have two parts. One part is handled by the DBA who, working with the database developers, creates the necessary roles, schemas, and Windows group logins on the database instance. The other part is the security team who controls the people added (or removed) from the Active Directory group(s) so that the users can access the applications as needed.

That is the separation: the DBA doesn’t control the users in the groups, just the structure the groups are allowed to access. Likewise the security team has no say in how the structure inside the database instance is built, they just fulfill the requests for access after the IT custodian has approved.

Reduce your administrative overhead

Besides the likely compliance issues you will face, if you are adding in Windows logins by hand your actions will not be able to scale to hundreds of instances. I’m certain that someone will leave a comment telling me that Powershell can get the job done with just a few lines of code. This would be where I remind people that automation is a wonderful way to make mistakes faster than ever before.

Let’s consider the case of a developer who needs different levels of access for development, test, QA, and production servers. That would mean you need to keep four copies of your scripts for each environment, and server, and person. That is a lot of scripts to maintain and troubleshoot. I can’t even imagine trying to add or modify those scripts as needed.

Even with just a handful of servers the idea of trying to maintain the correct permissions on a per-user level is more overhead than I would want. Every time a new person joins the company I would need to recreate all the permissions necessary for that person to access the databases they need. And the minute a person gets an ‘access denied’ error message returned it would be in my lap to figure out what was missed.

I can’t imagine why anyone would prefer to spend their time making things more difficult than they need to be. With Windows groups your could have all the permissions, roles, and groups defined in advance and then the security team (remember them?) could add and remove users as needed.

Less mess to clean up afterwards

One area I see customers and clients struggle with centers around the removal of logins after a person has left the company or even changed jobs within the company. When there is no defined removal process the end result is a glut of logins defined on an instance of SQL Server and most DBAs have no idea who should stay or who should go.

When companies have a defined access process that is tied to Windows groups it helps to avoid the issue of having logins lingering around for months and possibly years. We’re talking about one step versus hundreds of steps. Which would you prefer?

If you think not having separation of duties would make an auditor have cause for concern just sit across from the table and look them in the eye and say “those logins have been there for years, we rarely go back to remove them, we wouldn’t know which ones are no longer valid”. Good times.

Exceptions are not the rule

There are always going to be exceptions to using Windows groups. And for those exceptions you will have the extra overhead, you will need a defined cleanup process, and you will likely need to file for an audit exception. But they are exceptions, not the rule. Exceptions always introduce complexity into your environment. Unless you enjoy having an environment more complex than necessary, unless you enjoy having failed audits, you should start using Windows groups as your standard.

If you only have a handful of servers, and your shop doesn’t need to comply with audit guidelines, then you can get away with continuing to use Windows logins versus groups. But at some point you will cross a threshold where the overhead for maintaining your environment outweighs the usefulness. And it is then, at the precise moment, when you will look back and say “crap, I should have listened.”

You’ll thank me then. And you’re welcome.

Share this: Twitter

Facebook

LinkedIn

Reddit

