I see posts often regarding questions for what rights should be granted and to whom with regards to SQL server. On this post I am going to address what rights should a DBA have to the Server itself based on the following question recently from the MSDN Forums:
If you read the above post, you will see that my answer is different from start to finish on the thread. At first I would have said that a DBA needs to be a Local Administrator on their server, but this really doesn't stand up to the whole idea of least necessary permissions, that as a DBA I enforce myself. I happen to be a Local Administrator on my servers at my job, but that really probably is overkill, and could be reduced. So what rights would a DBA need to a SQL Server at the OS level?
This is determined and or dictated by the definition of the role of a SQL Server DBA. As a SQL Server DBA, there are certain things that I expect to be able to see and or do, and that I would argue belong within my realm of responsibility. A short list of these tasks would be:
- Monitoring Performance Counters
- Managing Database Files
- Managing SQL Server Updates and Service Pack Installations
- Viewing System Event Logs
- View active Processes on the Server through PerfMon or TaskManager
- Start and Stop SQL Services
- Monitor Available Disk Space
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 job on the line if the server goes down from a mistake they made as a local administrator by goofing with something they shouldn't have. I personally like having the rope to hang myself, but I can also use that same rope to climb out of a valley in the event of an emergency as well. I personally know my limits, and I know when to involve my server team members with looking at a problem. This may or may not be due to my past experience as an NT4 and Windows Server 2000 Administrator, it was something close to 8 years ago, but I know where to draw the line of responsibility.
So where do you sit on this subject? I am interested to know if you are a DBA that doesn't have Administrator Access to the Windows Server, how you created permissions to do the above. How would one go about granting the individual rights required to be a DBA without being a Local Administrator? I can only figure out how to grant a subset of the above, but as I said previously, I am not a Windows Administrator, MCSE, or any other sort of subject matter expert when it comes to security outside of the SQL Server database engine itself.