Re: Builtin Administrators Group and SQL Agent Jobs

From: Matt Troska (matt.troska_at_co.mcleod.mn.us)
Date: 11/03/03 

Date: Mon, 3 Nov 2003 09:04:22 -0800

I tripple check my setup. Every thing seemed to look
correct. Now it is working. I must have missed a step
somewhere.

Thanks.

>-----Original Message-----
>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
>| >> >
>| >> >
>| >> >.
>| >> >
>| >
>| >
>| >.
>| >
>|
>
>.
>

Relevant Pages

  • Re: BULK Insert Problem
    ... Vyas, MVP (SQL Server) ... I Log on to my system using the Local System Administrator Account. ... And If I Try to Bulk Insert any thing from my ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Agent cannot run DTS packages that contain Analysis Services tasks
    ... >The source of the problem seems to be the Windows account the agent runs as. ... >into an administrator on the server where MS Analysis Services is installed. ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: xp_cmdshell issue, local system
    ... So initially I tried to change the login using EM, ... account to the localsystem for SQL Server and the same for agent which worked ... Now when I go and try the same for the agent startup account it ...
    (microsoft.public.sqlserver.security)
  • Re: MSSQL$SBSMONITORING Login to Disabled Account?
    ... SQL Server installed, I can't have the tools you were using, can I? ... I still don't know what that process is doing trying to login to the ... Administrator account and I don't understand why this login attempt is ...
    (microsoft.public.windows.server.sbs)
  • Re: Problems changing the password for the service account in SQL
    ... It is because there is BULTIN\Administrstors Login that alllow access to ... You have a domaim account group that SQL Server ... this domain account in SQL Server ...
    (microsoft.public.sqlserver.security)