I'm looking over the comments about DBAs and local Administrator rights and I noticed an interesting trend: no one discussed the separation of duties aspect. Basically the comments that were returned either said, "DBAs should have that knowledge," or "You can't do the job without it because..." so let's took a look at some of that.
Separation of Duties
This is one of those security things we often don't like hearing. That's because it usually translates to, "I'm going to restrict your rights." But there is some solace in this. Having come from the situation where I had all the rights, because I was the infrastructure/security architect and directory services administrator, to one where my rights were reduced because I went back to support development as a DBA, several folks wondered if I would be okay with not having all the rights I once did. Here's my thoughts on that:
Make DBAs Admins Over the Server
I can tell you from my personal experience this typically doesn't work in larger environments. In larger environments servers should be templated with a specific standard for build and configuration. And that can change infrequently. So you've got folks who don't do day-to-day management across the larger inventory having to keep in mind that standard configuration while attending to their DBA duties. And whenever you change the standard, you've got to brief the DBAs and make sure they understand said standard. This can start to go south in a hurry and pretty soon you're left with a server that's out of standards, that will be an audit point, you get the idea. I'm not saying it can't happen with system administrator types. It can. But the knock against system admin types have SQL Server access is they don't do it on a day-to-day basis and may touch something they shouldn't or make a change that's not very smart, even if they have been trained to manage SQL Server. The argument also must be applied in reverse. While something could be handled via GPOs and other automated enforcement mechanisms, not everything can. And this goes beyond allowing an end user access to sensitive data. It could also mean misconfiguring the server in such a way that increases its attack surface unnecessarily or some other change that makes the server more susceptible to attack and compromise.
The MCDBA Covers Windows Security
Yes, it does have a server OS exam as a requirement. But as I indicated, there's a huge step up from passing that exam and knowing how to manage servers in a larger environment. And if you look at the newer MCITP tracks for SQL Server, this is no longer the case. So I don't assume an MCDBA can administer a server any more than I assume an MCSA or even an MCSE can. I know, I know, that's an old discussion about paper certs, but it's a valid one.
The Support Issue
I know that no one likes to be jockeyed back and forth between teams. However, even in cases where DBAs have had admin access, I have seen this happen. "Looks to be a server configuration issue, so I'm going to have to toss it over to the server guys." "But don't you have admin access?" "Yeah, but ultimately they have final responsibility and I would feel better if they took a look at it." So this scenario can happen regardless. If the issue is that the server folks aren't responsive, that's an organizational issue. Because if it's happening to the DBAs, then it's likely happening to everyone. And that means management needs to correct the issue by non-technical means. Granting DBAs admin rights doesn't solve the problem.
From a perfmon perspective, there are new local groups as of Windows Server 2003 that permit access to Windows performance counters without being a member of the local Administrators group. DBAs should be in these groups. In larger environments, there should already be automated monitoring solutions in place, which aggregate the data and display it in a better format than most of our manual tools. In that case, DBAs should be given read-only access to those tools and narrowed down to the SQL Servers wherever possible. So server monitoring would not be an argument for administrative rights.
This comprises two aspects: remoting into a server and remotely doing certain functions. Remoting into a server can be handled by Remote Desktop Users, which is a group designed to give RDP access without being a member of the local Administrators group. Other solutions establish what appears to be a local login and typically have security configuration options in place to select who can access via that remote access solution. With respect to management of services, etc., what's needed are power users rights. That precludes the need for local Administrators membership.
This depends on what you're trying to do. However, not being a member of the local Administrators group doesn't suddenly stop you from running scripts. Scripts can be put in and run from SQL Server Agent, for instance. Now what a script can do is restricted by what access one has. But that's the whole point.
It Can't Be Done
The argument that one can't be a SQL Server DBA without administrative rights on the server doesn't fly. There are plenty of environments where this is the exact situation. I didn't say it was easy. I also didn't say there wasn't headaches. But the fact of the matter is that it can be done. This just isn't a valid argument.
None of this is an argument that in all cases DBAs should be removed from the local Administrators group. I'm not saying that all. And I'm certainly not suggesting that's a good idea in smaller environments where the DBA may be the part-time or backup system administrator. But there are a lot of myths out there as to why DBAs need administrator rights and with rare exceptions, it's just not true. Having administrator rights can often make the job easier, simplifying troubleshooting, and reducing time to solve issues. It also gives DBAs more latitude to determine if something they are seeing is really the SQL Server or outside of SQL Server. But if we're going to go down the road of locking things down as tightly as possible, such as is the case when we start worrying about local Administrators getting into SQL Server, then we need to seriously consider pulling administrator rights away from anyone who doesn't absolutely need it, and that includes the DBAs.