February 21, 2008 at 12:50 pm
I work for a company where we support the servers of multiple companies. Most of the times they accept our best practices and dbas and give us local admin privileges for support purposes. We've run into one sysadmin that thinks DBAs should not have administrator privileges. A while back I got with our local microsoft rep and we came up with this list of privileges a DBA would need to have if they could not be granted the local admin privs. Please help me come up with rationale for each of these and how they are required to make SQL Server work.
b. DBA
• For most efficient use of DBA time for administration, maintenance and operations, the DBA should be granted a minimum of local administrator rights on the database server.
• If administrator rights are not possible the following rights need to be granted to the DBA account used on the database server:
o Ability to start/stop services
o Ability to install patches, service packs and hot fixes as needed.
o Act as part of the operating system
o Bypass traverse checking
o Lock pages in memory
o Increase Quotas
o Log on as a Service
o Log on as a batch
o Create a token object
o Replace a process level token
o Add/Refresh a database to active directory (if OLAP/Analysis Services is being used)
o Manage a database OU within active directory (if used)
o Access this computer from the network.
o Force shutdown from a remote system.
o Profile system performance
o Shutdown the system
o Backup Files and Directories
o Restore Files and Directories
The account chosen should have full control over the following Registry keys:
• HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\MSSQLSERVER
• HKEY_LOCAL_MACHINE \System\CurrentControlSet\Services\MSSQLSERVER
• HKEY_LOCAL_MACHINE \Software\Microsoft\WindowsNT\CurrentVersion\Perflib
If it is a named instance, then the first two keys will be similar to the
following:
• HKEY_LOCAL_MACHINE \Software\Microsoft\Microsoft SQL Server\
• HKEY_LOCAL_MACHINE \System\CurrentControlSet\Services\MSSQL$
NOTE: This complete configuration needs to be tested in your environment prior to using with any production database system.
c. SQL Server Service Accounts
Microsoft’s base recommendation for Windows authentication is to use a windows domain user account with the privileges listed below:
Act as part of the operating system;
Increase quotas
Logon as a service
Logon as a batch and
Replace a process level token.
If you are using ActiveX or CMDExec jobs, a multi-server administration master, mixed mode or require frequent password changes on this account, you cannot use a regular domain account.
In mixed mode environments where it is not possible to run under Windows only authentication, establish a separate account for running SQL Server Services. This account should have administrator level privileges plus the ability to
Act as part of the operating system;
Increase quotas
Logon as a service
Logon as a batch and
Replace a process level token.
February 22, 2008 at 9:12 am
this is a subject which is raised every so often - usually by auditors, suits or bean counters, none of whom understand databases, technology or our/your business.
I have a numbe rof short answers:-
If you need a DBA to administer a server fully he/she must be sysadmin on the box ( this will give the dba the same rights as the service(s) the sql server runs under )
If you need your dba to trouble shoot things like disk space, hardware monitoring and other basic o/s stuff they will need to be an administrator of the server.
If you don't trust your DBA sack him/her and get someone you do trust.
Lists are pointless, they either trust you to do the job you're paid to do or they don't. If you don't have all the rights who does and who do you go to when you can't carry out an administrative task?
somebody save us all from idiots!! On the othe rhand I've met DBA's I'd never give any rights to
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 22, 2008 at 9:22 am
I just hate dealing with this type of mentality. it's such a time waster to have to engage someone outside of a DBA group to do our job.
I'm just trying to provide a list of what is needed to administer SQL Server. The guy clearly doesn't want to cooperate, so I'll send him all the problem tickets.
February 22, 2008 at 12:49 pm
colin Leversuch-Roberts (2/22/2008)
this is a subject which is raised every so often - bean counters
What exactly is a bean counter?
February 22, 2008 at 12:54 pm
Bean counters are accountants who figure out if you get to keep your job usually 😉
February 23, 2008 at 9:29 am
colin Leversuch-Roberts (2/22/2008)
If you need a DBA to administer a server fully he/she must be sysadmin on the box ( this will give the dba the same rights as the service(s) the sql server runs under )If you need your dba to trouble shoot things like disk space, hardware monitoring and other basic o/s stuff they will need to be an administrator of the server.
Not strictly true - SQL really shouldn't be running under a local admin account, and you can grant some (all?) rights through group policies (such as perfmon).
However in general terms my opinion is that the DBA should be a local admin. I expect that most sysadmins would grant admin rights just to avoid the hassle of setting up all the granular permissions needed to do the job properly. And if they don't, make it clear who will be getting a call when you need more rights at 2am.
February 23, 2008 at 10:40 am
I agree with Matt that the service account does not need to be an admin. It could, but not as a general rule. I pick a regular domain user account and then let SQL Server assign the permisions needed using EM/Configuration Manager depending on version.
I've been local admins as a DBA and not and it doesn't matter as long as it doesn't interfere with my ability to get things done. If you don't want me to be an admin, then I need an admin to babysit around when I need Perfmon or some other tools run.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 25, 2008 at 12:49 pm
umm - I don't want to be picky here but I actually said the DBA needs to be sysadmin and also a member of the local administrators group - nowhere did I say the sql server should be running as a local admin !!
That said it's usually far easier to set the service account that way - avoids making specific folder and registry settings. I recommend removal of the builtin admins group from sql server and restricting users rights, especially as sysadmins or running xp_cmdshell.
However, as I merely support/consult/contract I don't usually get much say in how permissions are set, sadly. It's usually "consultants/auditors" who have these somewhat absurd requests - you should work with SOX where they didn't want the DBA's to have sysadmin rights - quite how we were expected to manage the servers was never explained. If you are supporting a production system and you are where the buck stops then you need all the rights.
You also need to gain the respect of those whose systems you support.
Tricky! Best of luck.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 25, 2008 at 1:01 pm
simple solution
put away your list and simply say OK, i will live with whatever privileges you give me. than when things go wrong, pester him with emails and phone calls that you would love to troubleshoot the issue, but have no access to the system
February 25, 2008 at 1:13 pm
... and, just to drive that well made point home... several panic calls at 2 - 4 am should quickly demonstrate the merit of a DBA having the privs to maintain their own server 😉
--Jeff Moden
February 25, 2008 at 1:55 pm
As far as I know to INSTALL SP or HotFixes you do need local admin rights 😉 There are also registry settings that are not commonly changed but that could come in handy on occasions 😉
* Noel
February 26, 2008 at 3:03 am
In SQL Server 2000, the SQL service account needed to be local admin, otherwise little things like Windows authentication, etc, etc, etc stopped working. Likewise some bits of EM seemed to only work if the person using it was also a local admin.
In SQL Server 2005 the SQL service account does not need to be local admin. Likewise the DBA does not need to be local admin to do their day to day job. However, the DBA does need access to an account with local admin rights for some troubleshooting.
The overwhelming trend outside of the SQL Server world is for product administrators to not have local admin rights for their day to day responsibilities. DBAs just have to accept that best practice has moved on, and if they insist on local admin rights for their day to day tasks they will be shown as a security anomoly and a risk.
The most sensible way forward is to sort out the rights needed for day to day tasks, then sort out getting quick access to a local admin account for troubleshooting. This way you work within best practice, and avoid being at the wrong end of security reviews. If the security/audit people do not like you having local admin for day to day tasks on SQL Server 2000 boxes, get them to support the move to 2005!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 26, 2008 at 2:10 pm
Well said Ed. :satisfied:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply