Grant permissions to run an SQL server job

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.

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.

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

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

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.