What is best way to allow a user to start an ad hoc job?

I have a job in SQL Server 2008 that I want to start by invoking it with sp_start_job. I have no problems starting the job when logged in as a user who has the sysadmin role, but I can't start it from a user that doesn't. This makes sense from what I have already read where sysadmins have access to everything. The error I get is

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67 The specified @job_name ('Extract Current Invoices') does not exist.

From what I have read I can raise an alert that will then trigger the job. I can also create a staging table to indicate that the job should run and then modify the job to continuously poll this table. Personally, I just want to run the job directly, but I am not sure that roles need to be opened up to the user to make this happen. Any ideas? Obviously, I don't want to make this user a sysadmin. I am thinking one of the SQLAgent roles or via proxy, but I am not sure if this could open up a security threat.
more ▼

asked Aug 29, 2011 at 07:44 AM in Default

wpuclark gravatar image

21 2 2 3

I think I might have answered my own question. When I added the SQLAgentOperatorRole to the user in question I was able to start the job with that user. Now I just need to make sure that this is a secure choice.
Aug 29, 2011 at 08:00 AM wpuclark
The operator role will let them run all jobs, you only want them to run one, so go for less permissions as @Magnus has mentioned
Aug 29, 2011 at 08:25 AM WilliamD
Actually, we are looking to give the user access to more than one job. Instead of going down the route of creating a stored procedure for every job, which is doable, are there any concerns of give the user the SQLAgentUserRole role and then give them ownership of the jobs?
Aug 29, 2011 at 09:02 AM wpuclark

If you want the user to be able to alter own jobs, create new jobs etc then SQLAgentUserRole would fit.

The stored procedure I suggested can easily be altered to take the Job Name as a parameter, have execute granted for everyone, and have it check against a table that you create, which jobs the current user has permission to run. Like this.

CREATE TABLE JobRunners (ID int identity(1,1) PRIMARY KEY, JobName nvarchar(100), loginname nvarchar(100))

CREATE PROC RunJob(@MyJobName nvarchar(100))
WITH EXECUTE AS 'loginname'
IF EXISTS(SELECT * FROM JobRunners WHERE JobName=@MyJobName and loginname=SYSTEM_USER)
    exec sp_start_job @job_name = @MyJobName

I would prefer doing that rather than giving the user permissions to the actual job, but that's because I wouldn't ever want to have "my" users creating jobs on their own.

A job can only have one owner, but with "my" solution, more than one user can be allowed execute permissions.
Aug 29, 2011 at 10:52 PM Magnus Ahlkvist
Of course, I should have realized that you could just pass in the job_name. This is exactly what I was looking for, and I like restricting the user by using the WITH EXECUTE AS instead of giving them access to the role.
Aug 30, 2011 at 05:59 AM wpuclark
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I wouldn't recommend you to add a user to SQLAgentOperatorRole just to let them run a specific job.

Instead I would create a Stored Procedure, which uses WITH EXECUTE AS 'loginname' to execute the job, using a 'loginname' that has permissions to run the job, and then grant EXECUTE for that stored procedure to the user.

Like this:

WITH EXECUTE AS 'loginname'
exec sp_start_job @job_name = 'Extract Current Invoices' 
Replace 'loginname' with a login that has permission to run the job.
more ▼

answered Aug 29, 2011 at 08:14 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 29, 2011 at 07:44 AM

Seen: 7109 times

Last Updated: Aug 29, 2011 at 08:07 AM