Re: Builtin Administrators Group and SQL Agent Jobs

From: Jyothi Pai [MSFT] (jyothip_at_online.microsoft.com)
Date: 11/01/03

  • Next message: Yoshihiro Kawabata: "Re: How to deny DBCC SQLPERF(logspace)" 
    Date: Sat, 01 Nov 2003 00:44:11 GMT

    Hi Matt,

    Please see my comments inline.

    >> On my SQL 2k Server I added a SQL Login for a Domain group for DBA's and
    gave that login full access to all SQL DB's. The DBA group is a member of
    the local
    >> administrators group as well.

    You granted access to SQL Server to a Windows NT domain group called DBA
    group and rights to all the SQL Server databases. This domain group is also
    a member of the local admins group on the SQL Server box. Did you add this
    login to the System Adminstrator Server role or any other Server role? You
    need to if you want to give these users system administrator privileges
    within SQL Server.

    >> I then removed the builtin\administrators group login from SQL Logins.

    If you remove this login, then the above Windows NT group will no longer
    have system admin privileges unless it also belongs to the System
    Administrator Server role explicitly.

    >> SQL Server and SQL Agent are both started by a user that is a member of
    the DBA group.

    Since the startup accounts for both SQL Server and Agent are started by an
    account that is a member of the DBA group and this group belongs to the
    Local Windows Admin group, they should be have NT local admin privileges
    but no admin privileges on the SQL Server box unless this account also is a
    part of the System Administrator server role.

    >> After I did all of the above, my SQL Agent Jobs no longer seem to work.
    What happens is the jobs "execute" but don't actually exeute. The jobs
    don't fail, but yet
    >> they don't complete either. SQL is installed in Mixed mode and all jobs
    are owned by SA.

    The security context in which the job is run is determined by the owner of
    the job. If the job is owned by an account (either a SQL Server login or a
    Windows NT authenticated login) that is a member of the Sysadmin role, the
    SQL Agent job runs under the context of the account used to start the SQL
    Agent service. In your case, since the jobs are owned by SA, they would run
    under the Security context of the SQL Agent startup account (which is the
    same as the SQL Server startup account in your case). Looks like the
    startup account does not have admin access to SQL Server.

    >> I have tried recreating the jobs and that did not seem to help.
    >> I added the builtin\administrators group as a SQL login and the started
    to work.

    This would work because, the SQL and Agent startup account which is part of
    the Local NT administrators group will now have Administrator access to SQL
    Server as the Builtin\Administrators login represents the Windows NT 4.0 or
    Windows 2000 local group Administrators. SQL Server Agent service connects
    to an instance of a local SQL Server to execute any job and the account
    must have system administrator permissions within SQL Server.

    >> I need to remove the builtin\administrators as a SQL login to restrick
    access to some of the DB's.

    In this case, please include the DBA domain group login to be part of the
    "System Administrators" server role on the SQL Server.

    Go to -> SQL Enterprise Manager -> Security -> Logins -> Look for the DBA
    group logins -> Properties -> "Server Roles" tab -> Make sure to check
    "System Administrators" Server role.

    This should give the startup accounts system administrator rights on the
    SQL Server. You can then remove the Builtin\administrators. Try again and
    see if that helps.

    Another point about the Builtin\administrators login, on a stand-alone
    server that is running SQL Server, you can remove the
    "BUILTIN\Administrators" login from SQL Server to limit this type of
    access. However, on a clustered SQL Server, the removal of the
    "BUILTIN\Administrators" login might cause problems. If the server is
    clustered and you remove the "BUILTIN\Administrators" login, the SQL Server
    resource goes into an online pending state and eventually fails. However,
    if you remove the "BUILTIN\Administrators" login before you run the
    Failover Cluster Wizard, the process completes successfully because the
    Failover Cluster Wizard explicitly adds a SQL Server login for the accounts
    that need access.

    For more information, please see the article:

    INF: How to Impede Windows NT Administrators from Administering a Clustered
    SQL Server
    http://support.microsoft.com/?id=263712

    Hope this helps.

    Regards,
    Jyothi Pai
    Microsoft Online Support Engineer

    Get Secure! – www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Matt Troska" <matt.troska@co.mcleod.mn.us>
    | Sender: "Matt Troska" <matt.troska@co.mcleod.mn.us>
    | References: <0b5401c39fca$6b8cddf0$a601280a@phx.gbl>
    <#2czBx8nDHA.3024@tk2msftngp13.phx.gbl>
    <0cdf01c39fcd$f7715e60$a401280a@phx.gbl>
    <O1tWbG#nDHA.2140@TK2MSFTNGP09.phx.gbl>
    | Subject: Re: Builtin Administrators Group and SQL Agent Jobs
    | Date: Fri, 31 Oct 2003 11:36:54 -0800
    | Lines: 96
    | Message-ID: <00a701c39fe6$56e3ccd0$a601280a@phx.gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Thread-Index: AcOf5lbhlexEpbsfThqhaPf9ooqi6Q==
    | Newsgroups: microsoft.public.sqlserver.security
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.security:16686
    | NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
    | X-Tomcat-NG: microsoft.public.sqlserver.security
    |
    | I checked and 'Change a process level token' and 'Act as
    | part of the OS' were not granted to the service account.
    |
    | I granted those rights to the account, but it still does
    | not seem to be working correctly.
    |
    | Matt
    |
    |
    | >-----Original Message-----
    | >has the service acct which sqlagent and sqlserver run
    | been given the
    | >following rights?
    | >
    | >- Change a process level token
    | >- Act as part of the OS
    | >- Log on as a service
    | >- Increase Quotas
    | >
    | >?
    | >
    | >
    | >--
    | >Kevin Connell, MCDBA
    | >--------------------------------------------------
    | >The views expressed here are my own
    | >and not of my employer.
    | >----------------------------------------------------
    | >"Matt Troska" <matt.troska@co.mcleod.mn.us> wrote in
    | message
    | >news:0cdf01c39fcd$f7715e60$a401280a@phx.gbl...
    | >> All jobs are owned by SA and the problem remains.
    | >>
    | >> >-----Original Message-----
    | >> >if possible, try changing the job owners to "sa" or an
    | >> appropriate non-NT
    | >> >account.
    | >> >
    | >> >
    | >> >--
    | >> >Kevin Connell, MCDBA
    | >> >--------------------------------------------------
    | >> >The views expressed here are my own
    | >> >and not of my employer.
    | >> >----------------------------------------------------
    | >> >"Matt Troska" <matt.troska@co.mcleod.mn.us> wrote in
    | >> message
    | >> >news:0b5401c39fca$6b8cddf0$a601280a@phx.gbl...
    | >> >> On my SQL 2k Server I added a SQL Login for a Domain
    | >> group
    | >> >> for DBA's and gave that login full access to all SQL
    | >> >> DB's. The DBA group is a member of the local
    | >> >> administrators group as well.
    | >> >>
    | >> >> I then removed the builtin\administrators group login
    | >> from
    | >> >> SQL Logins.
    | >> >>
    | >> >> SQL Server and SQL Agent are both started by a user
    | that
    | >> >> is a member of the DBA group.
    | >> >>
    | >> >> After I did all of the above, my SQL Agent Jobs no
    | >> longer
    | >> >> seem to work. What happens is the jobs "execute" but
    | >> >> don't actually exeute. The jobs don't fail, but yet
    | >> they
    | >> >> don't complete either. SQL is installed in Mixed
    | mode
    | >> and
    | >> >> all jobs are owned by SA.
    | >> >>
    | >> >> I have tried recreating the jobs and that did not
    | seem
    | >> to
    | >> >> help.
    | >> >>
    | >> >> I added the builtin\administrators group as a SQL
    | login
    | >> >> and the started to work.
    | >> >>
    | >> >> I need to remove the builtin\administrators as a SQL
    | >> login
    | >> >> to restrick access to some of the DB's.
    | >> >>
    | >> >> Thanks.
    | >> >>
    | >> >> Matt
    | >> >
    | >> >
    | >> >.
    | >> >
    | >
    | >
    | >.
    | >
    |

  • Next message: Yoshihiro Kawabata: "Re: How to deny DBCC SQLPERF(logspace)"

    Relevant Pages

    • Re: dsn-less connection
      ... It worked pretty much as I was hoping in prompting the user for a SQL ... Server login and was able to remove the specific dsn that's in the odbc ... straight to the SQL Server login prompt? ... with the login prompt but the Use Trusted Connection is checked on. ...
      (microsoft.public.access.security)
    • Re: Logging in irrespective of database access
      ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
      (microsoft.public.sqlserver.server)
    • Logging in irrespective of the database access settings
      ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
      (microsoft.public.sqlserver.server)
    • Re: Logging in irrespective of database access
      ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
      (microsoft.public.sqlserver.programming)
    • Logging in irrespective of the database access settings
      ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
      (microsoft.public.sqlserver.setup)