This was actually spurred by a post from Ted Krueger (@onpnt), which led to a short, but hearty, discussion on Twitter. He was discussing removing access from a local server admin to the SQL Server. My discussion was in regards to the fact that this isn't a successful preventative control. A system administrator who knew what he or she was doing can bypass the lack of access and still get access to SQL Server. I've gone into how previously, so I won't rekindle that debate here. But that does raise another question, and another controversy.
Should DBAs have local administrative rights on their SQL Server? DBAs would argue that it makes their jobs easier. Indeed, it does. But server admins and security folks would argue that it makes their jobs harder. I've been on both sides. I'm back as a senior DBA now, but I spent the previous seven years as an infrastructure and security architect. I've seen some crazy stuff done by folks who have admin rights and who don't understand the implications of what they are doing. Notice I was quick not to limit it to DBAs. Case in point, a particular IT pro (not a system administrator type) created a share on a production system where Everyone had read permissions through the share and at the NTFS level Authenticated Users had read rights as well. Meaning anyone on the domain could access that share. And that share periodically contained sensitive information. Internal audit caught it and flagged it immediately. But you get the idea.
The argument against system administrators having rights inside SQL Server is that they don't necessarily have a full understanding of what they are doing. But couldn't that same argument apply in reverse? Yes, yes, it could. When I start talking about GPOs, about where things are in registry, about what services are critical, about NIC configuration, about shares and NTFS permissions, quite a few DBAs start getting that glassy-eyed stare. The same stare you get when you start talking recovery models, rebuilding indexes, securables, and the like when talking to most system administrators. So if the argument applies in one direction (no to sysadmins because of the lack of knowledge), it must apply in the other (no to DBAs because of the lack of knowledge). Meaning DBAs aren't local administrators on the SQL Server (and incidently, neither is the SQL Server or SQL Agent service, since it's a simple matter to privilege escalate using them if you're a DBA). Yeah, I said it. I know it's not popular. But it's the logical argument carried back in the other direction.
Another point that is made to keep system administrators out is separation of duties. Sysadmins shouldn't be touching the SQL Servers because it's not in their job duties. As a matter of fact, to prevent one person from stealing everything, the duties are split and so are the permissions. Now, realistically this doesn't work, but it's a good argument. And if it's a good argument as applied to system adminstrators, it's a good argument when applied to DBAs. Meaning DBAs have the rights to their SQL Servers, but not to the servers themselves. Again, yeah, I said it. And again, I know it's not popular. But again, it's the logical argument carried back in the other direction.
So does this mean we should just forget the whole thing? Or does it mean we should just lock everyone down, start building trenches, and lobbing mustard gas at each other (though that's banned)? Well, it depends. Yeah, I said that, too. It depends on your organization. It depends on the data. It depends on the job functions. It depends on the other controls in place. There are enough factors that you can't give one of those "best practice" answers and move on. You really have to consider each and every situation independently. Is that a cop out? No, that's reality. I know of cases where controls say production DBAs can access a particular server but development DBAs can't. That's even more stringent. But if you're talking HR data or intellectual property, maybe that makes sense for your organization. But if you're going to consider stripping local admins from getting into SQL Server for security reasons, then I would think that if you're serious about security, and not just about building personal fiefdoms that you've got to look at the other side, too. You might come to the conclusion that DBAs need to main admin rights. And if that's fine with your organization, there's nothing wrong with that. Just as long as the question is considered in the first place.