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.

4 Answers 4


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

  • 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, 2009 at 11:57
  • @shaharmo: You can have the stored procedure execute as the database owner, I'll edit my answer
    – Andomar
    Dec 31, 2009 at 12:12
  • 1
    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, 2009 at 12:28
  • 1
    @shaharmo: Strange, I wonder who the database owner is? You can also use with execute as 'loginname', does that work?
    – Andomar
    Dec 31, 2009 at 12:31

This will allow a specific user (user1) to run any Sql Agent job.

Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users

USE [msdb] GO CREATE USER [user1] FOR LOGIN [user1] GO USE [msdb] GO ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [user1] GO


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


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

  • 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, 2009 at 11:54
  • 1
    Which is what you asked... "grant public permissions to the job"
    – gbn
    Jan 2, 2010 at 19:29

I have a similar need to grant permission for my operator to manually execute SQL Server Agent tasks. I followed this post SQL Server Agent Fixed Database Roles to grant the role SQLAgentOperatorRole.

Hope this help to solve your case.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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