Job Permissions - ODBC Error 14262 1

[SarahG]

I have a job set up on SQL Server 7 whose owner is set to be the NT account which runs the SQL Server service.
I have a stored procedure which runs this job (using sp_start_job).
I have an access 2000 db which calls this sp (exec sp_start_job @job_name='TestJob').

The sp & consequently the job are run with no errors from access when I am logged in.

However, when another user is logged in I get an error message :

[Microsoft][ODBC SQL Server Driver][SQl Server]The specified @job_name(‘TestJob’) does not exist. (#14262)

This is obviously related to permissions, but what am I doing wrong?
I know I can work around this by having the sp call a batch file which starts the job via isql, but there must be an easier way!!
What must I do to allow a regular user to run sp_start_job from a stored procedure?

 

[JamesLean]

The problem is with the owner of the stored proc, not the job.

I am assuming the owner of the SP is your login (SarahG?) and not dbo? If this is the case, either change ownership to dbo or qualify the SP name when calling it from Access:

EXEC SarahG.sp_start_job ...

 

[JamesLean]

Sorry, I've just re-read your message. Try the above, but if still same error, then you may need to also qualify the name of the job within the SP.

 

[SarahG]

Hi James,
I've been playing around with it in the meantime and cut the intermediate sp out of it, so from access I just run "exec msdb..sp_start_job @jobname='TestJob'", with the same error.
I can only get the regular user to run it if I make them the owner of the job. I need to make it available to more than one user, and the job screen doesn't give me the option to make a role the owner.
I think I will give up at this stage and go with my work-around, but if you come up with anything please let me know.
Thanks for your help.

 

[JamesLean]

Sarah, I've looked into this a bit more and found this info in BOL (2000):

A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.

I guess there's no way you can achieve exactly what you're trying to do. One suggestion I would make is to just put whatever the job is doing into a stored procedure and just execute that from your app. Is that possible?

 

[SarahG]

I had originally kept the tasks in a separate job, as I wanted to run bcp/bulk insert and ran into a similar issue, but I guess I will revisit it.

Thanks for the info on who has permissions to run it.