Re: Rights Change to Administrator Account Causes Hassles

From: Steve Thompson (SteveThompson_at_nomail.please)
Date: 01/05/04 

Date: Mon, 5 Jan 2004 12:49:21 -0500

Hi Karen, A few questions for you:

I'm not clear on why you are removing the sysadmin rights from the
Administrator login account? (I know you want to lock SQL Server down). Did
you define a "user" account that you have given appropriate permissions for
the services, NTFS and registry?

Are you referring to the 'sa' account or the local NT 'Administrator'
account?

If the latter, is that the account that you are running the MSSQLServer and
SQL Server Agent services?

Steve

"Karen R" <hgkritchey@hotmail.com> wrote in message
news:#J7p#k60DHA.3416@tk2msftngp13.phx.gbl...
> I am trying to tighten up security in my database(s) following
> suggestions from the MS advanced SQL admin class 2723. The first thing I
> tried, since I know it is not used, is to remove the system admin rights
> from the Administrator login account. This is on SQL2ksp3a.
>
> The SQL jobs in my database are owned by SQLAdmin, which is a systems
> admin account. The SQL agent service starts under local account. I
> removed the system admin rights from the Administrator login, and all of
> the the maintenance plan jobs stopped working. I get this error: "unable
> to determine if owner of transaction has server access - execute
> permission denied on
> sp_sqlagent_has_server_access database msdb owner dbo sqlstate 42000
> error 229"
>
> Since Administrator is not the owner of the job, or the account that the
> service starts in, or the owner of msdb, why does this happen? I cannot
> find any connection to the Administrator login that would cause this to
> happen. The Administrator login is not used for anything in the database
> that I have seen.
>
> I had this question posted in another forum, which may have been the
> wrong venue for it. The only reply I received was from Andy Svendsen
> (Thanks, Andy!) who suggested checking the "Run As User" option on each
> job. All the jobs run as self. He also suggested checking any Active X
> or steps that run a command shell in the jobs in question. All of the
> jobs are log & db backup, optimization, and integrity check jobs created
> with the maintenance plan wizard.
>
> I tried recreating each job, to no avail. I have had to return sysadmin
> rights to the Administrator login for my jobs to run.
>
> Thanks in advance for any ideas what else I can try!
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Jobs Failed do not work when scheduled done via DTS Scheduled Package
    ... the SQL info out within the PATH. ... running one of the Jobs and it executed without a hitch. ... > What does the DTS package do? ... the account used is whatever account you ...
    (microsoft.public.sqlserver.server)
  • SQL Agent Account
    ... There are a few jobs that developers create and they run under there ... I enabled the SQL Agent Proxy account yet the above ...
    (microsoft.public.sqlserver.security)
  • Rights Change to Administrator Account Causes Hassles
    ... suggestions from the MS advanced SQL admin class 2723. ... from the Administrator login account. ... The SQL jobs in my database are owned by SQLAdmin, ...
    (microsoft.public.sqlserver.security)
  • Re: Change service account with SQL server 2000 sp4
    ... I deleted the builtin administrator as systemadminstrator. ... It is a AD domain account. ... I then changed all SQL jobs to the new account. ...
    (microsoft.public.sqlserver.security)
  • Re: Rights Change to Administrator Account Causes Hassles
    ... I want to remove the SQL sysadmin rights from the NT Administrator login ... As to the "why," besides my goal of tightening up SQL ... Currently all services run under the "local" account. ... Most of the jobs are maintenance plan jobs, ...
    (microsoft.public.sqlserver.security)