x
login about faq Site discussion (meta-askssc)

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 '11 at 07:44 AM in Default

wpuclark gravatar image

wpuclark
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 '11 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 '11 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 '11 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'
AS
IF EXISTS(SELECT * FROM JobRunners WHERE JobName=@MyJobName and loginname=SYSTEM_USER)
BEGIN
    exec sp_start_job @job_name = @MyJobName
END

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 '11 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 '11 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:

CREATE PROC RunJob
WITH EXECUTE AS 'loginname'
AS 
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 '11 at 08:14 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
13.7k 13 17 30

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1601
x145
x82
x14

asked: Aug 29 '11 at 07:44 AM

Seen: 5027 times

Last Updated: Aug 29 '11 at 08:07 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.