Hey everyone, I have a rather unique set of security rules to follow
here and cannot seem to enforce them and keep my SQL Agent jobs
running on the SQL 2005 system (I have it working fine on SQL 2000).
I am no DBA, but I am the best my particular group has so I am the one
working this.

We are removing BUITLIN\Administrators from the logins, so local
Administrators do not have access to SQL.
We are also changing the sa password every 90 days (via a compiled
script).
I am trying to get the SQL agent jobs (backups and optimizations) to
run as the local NT_AUTHORITY\System account.

The event viewer tells me that is does not have server access:
"The owner [NT AUTHORITY\SYSTEM] of job
TransactionLogMaintenance.Subplan_1 does not have server access."

I have given it all the access I can figure out how to give it, but no
luck. It seems that once I remove BUILTIN\Administrators the SYSTEM
user loses access along with it, even though I specifically put it in
as a LOGIN with sysadmin role and I also ran this:

EXEC sp_grantlogin [NT Authority\System]
EXEC sp_addsrvrolemember @loginame = [NT Authority\System], @rolename
= 'sysadmin'

I could make the owner of the jobs sa, but then I would have to update
the properties and put in the new password every 90 days(right?). I
don't even know the sa password, it is locked up elsewhere. In order
for me to connect I have to have my bosses boss come over and log me
in.
However, for the time being I added my domain group to give me access
until I get this straightened out.

So, is it possible to have the jobs owned by SYSTEM the local Admin
group removed and still have the jobs run?
The SQL Agent service is running and it is connecting using the SYSTEM
account.
The backups are just to the local drive, so domain privileges are not
needed.

Thanks in advance!

On Apr 2, 12:49*pm, Tonagon <tony.robe... (AT) marriott (DOT) com> wrote:
I am no expect with the settings necessary on Windows but have hit an
issue or two including having to change the sqlagent service user back
to local to get it to run after upgrading to 2005 when it had been
working fine under 2000 with a user id. Our issues were due to a bug
and our being clustered.

I am thinking your issue might not be a SQL Server setting issue but
rather might be an OS setting issue. That is after you verify the
password case make sure that all necessary privileges are assigned at
the OS level. Someone may have removed one of the necessary OS
privileges/memberships that are expected to be there.

HTH -- Mark D Powell --

On Apr 7, 9:42*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
I think that I got things working. I set the owner of the sql agent
jobs to be sa.
Setting it there does not ask me for a password, just an owner.
So the Maintenance plans are connecting using the default Windows
authentication (use Windows NT Integrted Security).
The jobs created by the maintenance plans are owned by sa and the step
within the job that actually runs the stuff is left at the default
'use Windows Authentication" to log in to the server (local server).
The SQL Agent service is connecting using the default local system
account.

On 7 Apr, 22:01, Tonagon <tony.robe... (AT) marriott (DOT) com> wrote:
The way we have it running is our company is:
1)set up a dedicated Windows Acct for sql server agent jobs, this will
allow you to apply consistently your rules.
2)If you need to implement network transfers as part of the job - then
you can issue relevant folder permissions to the account