Re: DBA Access to SQL Server

From: John Alderson (jalderson.spamnot@adelphia.net)
Date: 03/02/03 

From: "John Alderson" <jalderson.spamnot@adelphia.net>
Date: Sun, 2 Mar 2003 11:41:01 -0500

"Bryan Ivie" <bryani@jeld-wen.com> wrote in message
news:04e901c2de89$8e52ebc0$a001280a@phx.gbl...
> I read the whole thread and have only one suggestion...
> if you have a reason, other than a personal control
> issue, for removing your sql dba from the local
> administrators groups on your servers, perhaps you should
> look at the qualifications or trust of your sql dba
> rather than his/her permissions.
>
> 99.9% of the time i hear this question it's a control
> issue coming from the system admin. and not warranted. I
> can't imagine trying to do my job without the appropriate
> permissions.
>
> Bryan
>
> Bryan Ivie
> SQL DBA, Jeld-Wen Inc.
> bryani@jeld-wen.com
>

Bryan,

I'll bet you work in a relatively small shop where you have multiple
responsibilities and broad access makes sense. In large organizations, the
separation of privilege and responsibility is a valid and often necessary
goal. You raise a great point in your last sentence re: appropriate
permissions. The fact is, day to day DBA work with SQL Server does *not*
require Local Administrator permission.

Mike, if your support model includes the DBA folks doing the SQL Server
installation, you'll need to give them administrator privilege at least for
installation so that they can create Services. After that, however, you can
easily bring them down to User level access. Here's roughly how I setup SQL
Server DBAs in a restricted environment:

1. Create a SQL Admins group locally if there is no appropriate Global
group. Populate this group.

2. Map this group to a login in SQL Server granting SA privs.

3. Give this group Modify (NOT Full Control!) access to the file system on
the SQL Server data, tranlog, backup volumes. Depending on what else is on
the box, either do this at the root or at MSSQL\. You don't have Everyone -
Full Control on these volumes, do you?!

4. Grant the SQL Admins the rights to stop and start the SQL Server and
Agent services. This is where most folks get hung up as there is no easy
interface to set this up. There are, however, a number of ways to do it.
Look at this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;288129

5. If the DBAs want to monitor performance, follow this article to set that
up for them:

http://support.microsoft.com/default.aspx?scid=kb;en-us;158438

6. If you want to allow them to logon to the box via Terminal Services
Remote Admin mode, permissions will need to be granted for access to RDP via
the Terminal Services Configuration applet.

This methodology works for us. In this environment, we also do the initial
SQL Server installation and maintain patch levels working with the DBAs for
functionality testing, etc. YMMV. We also run SQL Server as a
non-Administrative User in all cases, even Clusters.

This framework provides for very clear separation of duties and access to
resources. Folks working with Windows need to get over the "I must be Admin
to do my job" mindset. In the Unix world, for the most part, application
support folks simply don't get root perms for anything. This has become
part of the culture - to the benefit of unix installations. Instead of
focusing on being in the Administrators group, focus instead on the tasks
you need to do to do your job. The appropriate permissions will fall
directly out of that. In most cases, those permissions can be granted
without membership to the Local Administrators group.

John Alderson

Relevant Pages

  • Re: "Access denied" with xp_cmdshell (!)
    ... restart Sql Server for those permissions to take effect. ... permissions for an account won't affect processes already running as that ... File 3.bmp permissions are that all admins get full control. ...
    (microsoft.public.sqlserver.security)
  • Re: "Access denied" with xp_cmdshell (!)
    ... But problem is that I have tried to give for that account full control ... restart Sql Server for those permissions to take effect. ...
    (microsoft.public.sqlserver.security)
  • Re: Creating index - Error 229 (Permission denied)
    ... You do not have permissions to access sysjobs_view in database msdb and also execute sp_help_category in msdb. ... Your DBA needs to give you the correct permissions to see the schedules, ... I have my development database on a SQL Server 2005 server. ...
    (microsoft.public.sqlserver.fulltext)
  • RE: SQL and Account Permissions
    ... A couple of good recources on SQL Service Account permissions: ... SQL Server 7.0 Resource Guide Part 7 Chapter 10 has almost the identical ... .Full control of the main Microsoft SQL Server directory (by default, ...
    (Focus-Microsoft)
  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)