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.

Comments

Posted by Jack Corbett on 25 November 2009

I'm a big fan of separation of duties. At my last job I was a developer/DBA but had domain admin rights also. I rarely used them beyond resetting a password or unlocking an account. Anything on my SQL Servers that was OS or server related I asked the server admins to deal with.

Where I am know I do have local admin rights on the SQL Servers, but again I ask the server admins to administer the server. Just yesterday I was working with PowerShell and applied a script to a dev server and found out PowerShell was not installed. So, I asked the server guys to install PowerShell on the SQL Servers. I could have done it, but it really isn't my job.

Posted by Ron Sexton on 25 November 2009

I can see the arguments but in most cases it is better to have the DBA have local admin rights. Especially on Clusters. Sure you can work on seperating it out and if everyone you work with is responsive, responsible, and not over extended then it may work out well. Thats a lot of ifs. In my experience it usually doesn't work out well. Is it better for the DBA to not have local admin rights? If they truly have the luxery of only doing DBA work and it is working out well then great for them! As it is I am the Production DBA, Virtualization (especially for SQL Server), Server build (especially for SQL Servers and Clusters), Boot From SAN build (especially for SQL Servers and Clusters), HP Blade enclosure person (Stacking, Linking, firmware updates..), and Sysadmin building new domain controllers and retiring old ones and probably a few things I am not remembering right now. So I can certainly see where being able to specialize could be worthwhile as SQL Server is a huge product/topic onto itself that I cannot stay up on as much as I would like to.

Posted by Matt on 25 November 2009

Brian,

How do you handle installation of SQL Server service packs/patches in your environment? Are they the responsibility of the server team or the DBA team?

Posted by K. Brian Kelley on 25 November 2009

In our environment, it depends. SQL Server specific service packs and hot fixes are the DBAs' responsibility. Others belong to a group, security services, who is responsible for keeping on top of what's going on security-wise.

In our case, we maintain admin rights. Mostly. There's a few that we don't, like the server that monitors us. When we have to upgrade it, we put in a request, get temporary admin rights, do the upgrade, notify, and then the rights are removed.

This is not unusual in larger environments. For instance, I know one large organization where everything is deployed by QA. The QA person gets temporary administrator rights during deployment, but they are revoked otherwise.

Posted by David PROVOST on 29 November 2009

Interesting article.

In our Environment, DBAs are local admins, because Server guys lack responsiveness and have no SQL server skills. Like, we cannot

- access Job file logs,

- restart SQL Agent to endorse new security groups,

- diagnose access failure from a SQL service account,

- change a SQL password in an ODBC connection string (which must be synched with SQL user password change),

- check NTFS fragmentation on some backup files

- check server for partition misalignment

- create a new service account for SQL installation

- dump / restore a copy of a database to a pre-prod server.

... aso

One day or another, a DBA needs some info / action that requires admin privs. If server admins cannot provide it shortly (like processing my request as a VIP one), we'll be looking / waiting for each other (not in the same building) for, sometimes, 24h. Too bad when some production database is down.

Posted by David PROVOST on 29 November 2009

One thing should be precised : admins (system- or DB-) should never access servers with their office account. Use dedicated (still nomminative) account.