Should your DBA have local administor rights on his SQL Server

Anyone who's read any of my previous articles ( www.gre-sqlserver-solutions.com ) will know that I deem data as the most precious asset an organisation can have and protecting that data is critical.

But also everyone knows that security is a hot topic.

There seems to be debates raging over whether SQL Server DBAs should be given local admin rights to a server. Now I�ve scoured the depths of the abyss to find documentation to back up my claim that� Yes they should be given admin rights � but I couldn�t find any!

This article will, no doubt, fuel the argument further. It will aim to highlight why I get frustrated as a DBA when I�m given limited access to effectively what are � my SQL server boxes!!

It would be quite easy for me, as a flag bearer for the DBAs, to turn this around and say that the Windows Admin people/Infrastructure team have no argument to have rights as sysadmin on your machine!Whether they like it or not, SQL Server will always be closely intertwined with Windows.

A DBA should have full admin rights on SQL Server boxes and SA privileges. Non DBAs shouldn�t be associated with SQL admin responsibilities such as performance tuning, checking and fixing DB integrity, configuring SQL backups etc Or, if they are associated and do have even minimal knowledge about SQL server they should realise from a company perspective it's better to have cooperation between the infrastructure team and the SQL DBAs.

It may seem to be a petty debate and that politics take over here but it is an issue in some workplaces, but in my eyes needn�t be.

I�ve researched numerous blog posts/questions that pose the question �what rights should be granted and to whom with regards to SQL server�. I am going to try and address what rights a DBA should have on the server itself.Now I am conscious that some organisations define the role of a DBA completely different to others and depending on how they dictate that definition of the role of a SQL Server DBA � answers could vary!As a SQL Server DBA, there are certain things that I would expect to be able to do and that I would argue belong within my area of responsibility.Things such as managing database & log files, monitoring available disk space, monitoring performance(through use of counters), managing SQL Server Updates and installing service packs, viewing System Event logs, view active processes on the server through PerfMon or TaskManager, starting and stopping SQL Services. And probably a few other things.DBAs need access to the file system for copying files from production to the other environments, the ability to install software (SQL, Analysis Services, Reporting Services, etc), and the ability to log in via Remote Desktop.

Now arguably, the easiest way to accomplish the above is to make the DBA a member of the Local Administrators group on the SQL Server, and I would never argue against this practice, it is after all the DBA's door that gets knocked when the database isn�t performing adequately, when data itself goes missing etcOf course as DBAs, we�re also humans! So we are prone to goofing with something we shouldn't have. DBAs like having the rope to hang themselves, but we can also use that same rope to climb out of a self-created hole when needed too as well ! It�s all about knowing your limits, and when to involve server/system team members with looking at a problem. Where do you draw the line of responsibility?I will always continue to argue that DBAs wouldnt be able to do their jobs without sufficient access to the servers but trust me most of us would be quite happy to sit around and do nothing all day, whilst taking the pay cheque home each month!

Also we (The DBAs) are responsible for the database. If something happens to it, it is not the System Administrators who are going to fix the issue. The database might come down because of some stupidity caused by the System Admin. But we are blamed for it. It�s happened countless number of times in my organisation and no doubt will continue to do so, but we cannot ban the System Administrators from touching our DB Servers. However its ok for them to restrict our access!

OK, we may not need to be local administrators but at the very least need to members of Domain Users group as we will need certain permissions on the SQL Server and SQL Server Agent startup accounts.

There are certain rights the account would need (logon as a service, bypass traverse checking, lock pages in memory etc etc).Plus there are also registry keys we need full control over(HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\MSSQLSERVER, an obvious example).

Backing up and restoring databases these days are often a responsibility that needs to be shared � with the emergence of SAN technology etc, deciding upon who carries out the role of ensuring the data is actually backed up is a more difficult. You could be granted full server access to your SQL box but the power trippers in your infrastructure team may restrict your access to the back-end SAN. This is where compromise is called for and some kinds of cross-team responsibilities are needed.

Another grey area in terms of responsibility is the monitoring of SQL Server using Performance Monitor. DBAs should be able to gauge performance of their databases and to view how the OS is behaving in relation to SQL Server.

More often than not if there is an issue with a DB server, a Sys Admin guy would simply cure the problem by rebooting the server � but - without trying to locate the issue. However DBAs when trailing through logs with a view of hunting down the root cause are often vilified for accessing the production system.

I would like nothing better than to sit at my desk with my headphones foot-tapping away merrily without a care in the world for my organisations databases, but unfortunately when things stop working and question marks appear above heads my desk is the first port of call.

DBAs are the brains of the department. I often wonder what the other team's bodily function are...........

We had a situation in my organisation; we had a zealous sysadmin team who wanted to remove DBAs from the local admin group. They wanted to eliminate the DBA's access to the file system, right to install and configure software, and ability to login via Remote Desktop. They also believe that only they should have access to the Event Viewer and PerfMon. (They even argue that DBAs should not be permitted to use PerfMon!!) � sound familiar?

I agree though that it was their responsibility to give to each person just the minimal privileges needed to accomplish their job. Unfortunately, they went one step too far and moreorless defined the DBA's and other's jobs as they saw them. They argued (unsuccessfully) that database backups were not our (the DBAs') responsibility, but rather their (sysadmin's) duty.

Needless to say � this was changed when I took over at the helm!What OS privileges do DBAs have in your organisation? Domain admins, local admins, power users, or custom rights?If your sysadmins can provide a reason, beyond the fact that they are responsible for the OS, for not allowing at least the minimums to a DBA, then the DBA should have these rights because these things are also the responsibility of a DBA for SQL Server. So long as DBAs have these rights, they don't necessarily care if they are Administrators on the Server or not. It is the job of a DBA to ensure the SLA's regarding SQL and that the associated services are working properly.

As an aside, this is not just specific to SQL Server. My Oracle admins had issues gaining access to Oracle servers too. Since the database engine runs on the OS, you have to know about the OS also to be able to properly manage and administer the database engine.

Some (not all) server administrators can be over controlling people in one form or another trying to exert their powers etc. Imagine if we (DBA's) did it the same way, arguing why server admins should have no access to SQL Server in any way, shape, form, or fashion!? Its just pettiness isn�t it?

The whole �us and them� scenario of minimal privileges will continue to exist in organisations across the world, especially as security continues to be a major concern in the modern day. Typically I have found that what is actually required for compliance is a grey area and not many people understand it so you just get individual interpretations which tend to be a little over zealous Whilst I agree most DBAs are not Windows Administrators, MCSE, or any other sort of subject matter expert when it comes to security outside of the SQL Server database engine itself. Surely though, if DBAs were trustworthy/reliable, then they wouldn't be in the position they are in, or at least I like to believe that.

Of course I may be being a little harsh towards to Server Admins here as sometimes, those who dictate security policy within an organisation (such as Information Security Officers) believe that there is often a knee jerk reaction to lock everything down to the point where no-one can do anything which may make you system secure but all but unmanageable � and of course would introduce a more harmonised working environment!One of the possible grey areas is the appliance of patches and server updates. If DBAS are to be responsible for the overall operation, integrity, and reliability of SQL Servers, they must be able to check on whether Windows as well as SQL Server updates and patches have been applied. DBAs do not need to necessary apply Windows patches and updates, but they should be allowed to discuss the merits of applying them or not. DBAs should certainly have the final say on applying SQL patches and updates, and should also apply them personally. If DBAs must be restricted maximally, then the Admin can apply SQL updates and patches with the DBA watching over his shoulder at least.

Another subject for debate - does a DBA need permission to reboot a SQL Server? Rebooting a production SQL Server should require a joint decision made in advance, preferably with management approval, in which case it doesn't matter who actually does it. If either the Server Admin or the DBA reboots a production server without consulting the other or indeed management, depending on the outcome, his or her head might justifiably roll!

In the end, DBAs and Server Admins are on the same team. You (and your options) sink or swim together. If either of you feel the other is incompetent, that's a different matter. But if you both respect each other technically, you have to live with joint responsibility over the totality of SQL Servers, not necessarily infrastructure versions for Exchange and Sharepoint, but main line versions supporting the core business.If they don't, then I'm afraid conflicting attitudes do suggest an unpleasant and unproductive desire to do battle on the field of ego.

The problem is that people don't like giving up access, plain and simple. You probably won�t find a single technical answer on why a DBA needs Local Admin rights. Why is that? Because there isn't one. What it really came down to in my organisation is personality conflicts. And it was simply something which needed to be (and was) dealt with.

I also agree for some organisations, it's up to the server admins to keep the servers up. The best way to achieve stability to any system is to reduce the number of people who can cause the system to go down. The effect is that it makes troubleshooting and documentation easier, which in return causes your system to become more stable. This is especially important in cluster configurations. But the DBAs need to have access; by working together, stability can only increase.

I could write for pages and pages on why I think restricting DBAs from accessing SQL database servers is not a healthy option for any business, and no doubt Server Admin folk out there could quite happily counteract my views with pages and pages of their own.

The views in this article are somewhat personal and of course your business, the way your organisation works etc could govern the way you approach this problem. The powers that be, security teams etc may have taken away the need for debate before it can start Playing childish games of my stuff VS your stuff only benefits egos, not the company.