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:

• If there arose a situation where I needed those rights again, or where the organization needed me to have those rights again, it would be a simple matter of dropping me back into the right groups during the emergency and then pulling me back out once the issue was solved. This has happened a couple of times during the almost year since I made the transition, but they were towards the front of the transition.
• By not having those rights, I'm not on the hook for security checks and audits. This was the bane of my existence. Because I had access everywhere, I was audited everywhere. And my rights had to be validated as necessary everywhere. When you're trying to hold to SOX and other regulatory requirements, this can by quite time consuming, especially as one has to rejustify the reason for those rights. I know in smaller environments this probably isn't necessary, but in larger ones for those with all the keys, it absolutely is.
• By not having those rights, I had clear delineation where my duties end and others' duties begin. When I had access everywhere, it wasn't unusual that once I was brought onto an issue, for me to be expected to own the issue until it was resolved. While this sometimes meant the issue was resolved a lot faster, it also meant that in some cases someone who should be working on a portion of the issue and gaining valuable experience wasn't as proactive about participating. You can say this is a management issue, but really, it's a symptom of most IT shops where we always have more to do than hours to do it. It's easy to fall into the following mindset: "If someone else has the capability to run with it and troubleshoot the issue, I know I should be participating, but I've got end users with expectations in other areas, so I'll let that guy work on the problem, engage me if he needs me, and I'll burn some cycles on these other work items." Now that my rights end at the SQL Server, if the problem may be back on an application server, or with some security construct we've built in the domain, I troubleshoot to my line of demarcation, then transfer what I've found to the person on the other side.

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.

Server Monitoring

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.

Remote Access

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.

Scripts

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.