SQL Server Agent permissions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew (Andrew_at_discussions.microsoft.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 06:53:02 -0800

Hi,

I am trying to schedule backup jobs - seemingly very simple however, I seem
to be having an incredibly annoying problem!

I have a user (user A) who has the ability to run the BACKUP LOG statement
in question. If I execute the job as user A through Query Analyzer, it
returns successful and I can also backup the database as user A using
Enterprise Manager.

However, when I schedule a job to run the very same statement that I run in
Query Analyzer, it fails. The job is owned by user A (the one that is
running the statement in Query Analyzer). User A is db_owner of msdb and
master databases. User A is not a member of sysadmin fixed server role since
I don't want him to be. I have also tried other types of job, such as
executing stored procedures and these seem to be failing too whenever they
are scheduled. The SQL Server Agent Service is running as user A.
Everything is under user A as far as I can see and the job only fails when
scheduled. MSSQLSERVER Service runs as localsystem account. User A is a
Windows 2000 account.

Can anyone shed any light as to why my job is failing? The only way I can
get the jobs to run successfully when scheduled is if I give user A sysadmin
fixed role but from what I have read, that shouldn't need to be so?

We are running SQL Server 2000 SE with SP3a on windows 2000 SP4

If I look at the "Display Error Log" when right-click on SQL Server Agent it
says this:

[000] Unable to retrieve steps for job Backup Heat - Log - Append
[298] SQLServer Error: 14262, The specified @job_id
('34C58431-DDF0-458F-AC0B-DAC057185CA9') does not exist. [SQLSTATE 42000]

The job history says this for the failure:

The job failed. The Job was invoked by User sa. The last step to run was
step 1 (Step 1).

The error in the job history against the specific step (Step 1) says this:

Unable to perform a SETUSER to the requested username '' because the
username is invalid for database 'master'. The step failed.

Thanks in advance

Relevant Pages

  • Re: backing up sql server DB nightly
    ... There are 2 options to schedule a backup of database. ... Maintenance plans use SQL agent to schedule so ensure that SQL Agent service ...
    (microsoft.public.sqlserver.programming)
  • Re: database backup schedule not working
    ... On the schedules tab hit "New Schedule" ... You will want to backup your user databases, master, and model. ... > I'm running SQL server 2000 on a Windows Server 2003. ... > Database, selected the local disk as a backup destination, clicked on ...
    (microsoft.public.sqlserver.tools)
  • RE: SBS Backup Error 0X8007007A
    ... have you manually create a scheduled backup job using the ... NTBackup tool and see if the backup works. ... If the Task Scheduler service is not already running, ... schedule", ...
    (microsoft.public.windows.server.sbs)
  • Re: Data Protector 6.0 scheduling mysteries
    ... In our complex scenario with many hosts and devices, I wrote a backup ... As DP cannot schedule at 17:10, the backup was scheduled at 17:00 also. ... and data protection. ... The omnitrig process is what actually schedules the sessions, ...
    (comp.sys.hp.hpux)
  • Re: Scheduled backups fail to start
    ... the same as the backup tasks. ... Can you run this batch file as ... The backup job started on 07/10/2008 at 16.53.56.88. ... - When you scheduled your backup, ...
    (microsoft.public.windows.server.general)