Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a job on my MSSQL server 2005, that I want to allow any database user to run.

I'm not worried about security, since the input to the job's actual work comes from a database table. Just running the job, without adding records to that table will do nothing.

I just can't find how to grant public permissions to the job.

Is there any way to do this? The only thing I can think of at this point is to have the job constantly running (or on a schedule), but since it only needs to do any actual work rarely (maybe once every few months) and I do want the work to be done as soon as it exists, this does not seem to be an optimal solution.

share|improve this question

migrated from stackoverflow.com Jan 10 '12 at 14:38

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 11 down vote accepted

You could create a stored procedure that runs the job. You can use with execute as owner to run the stored procedure as the database owner. That way, the users themselves don't need permissons on sp_start_job.

create procedure dbo.DoYourJob
with execute as owner
as
exec sp_start_job @job_name = 'YourJob'

Grant execute rights on DoYourJob to allow people to start the job.

Of course, you can also put the contents of the job in a procedure, and grant rights to run that. That would allow more interaction, like displaying a result text.

share|improve this answer
    
This doesn't work. The non-privileged user can run the procedure, but the procedure cannot execute sp_start_job, because the user doesn't have permissions to execute it. I don't want the contents to be in a procedure, because I don't want the caller to need to wait for the job to end. –  Shahar Dec 31 '09 at 11:57
    
@shaharmo: You can have the stored procedure execute as the database owner, I'll edit my answer –  Andomar Dec 31 '09 at 12:12
    
Still not working. I get the same error. I tried to create DoYourJob in the msdb database, as well as in my database, with the same results. –  Shahar Dec 31 '09 at 12:28
    
@shaharmo: Strange, I wonder who the database owner is? You can also use with execute as 'loginname', does that work? –  Andomar Dec 31 '09 at 12:31
    
It does! Thanks a lot –  Shahar Dec 31 '09 at 13:22

Basically, permissions are needed on sp_start_job (see permissions section).

The roles are described in "SQL Server Agent Fixed Database Roles" (linked from above)

Edit, Jan 2012

After the anonymous downvote 2 years after I answered...

Read the question. It says

I want to allow any database user to run

then

I'm not worried about security

and also

I just can't find how to grant public permissions to the job

So OP's comments below contradict the question

share|improve this answer
1  
Won't this give all users permissions to run all jobs? I want them to have permission to run only one specific job. –  Shahar Dec 31 '09 at 11:54
1  
Which is what you asked... "grant public permissions to the job" –  gbn Jan 2 '10 at 19:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.