Grant permissions to run a SQL Server job

Question

I have a job on my SQL Server 2005 instance 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.

Answer 1

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.

Answer 2

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

Answer 3

The question 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

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)

Answer 4

Jobs run from msdb schema and there is 'use msdb' forbidden in procedures. Hence, the procedure can be typed like this:

CREATE procedure [dbo].[myJobToRun]
with execute as owner
as
exec [msdb].[dbo].[sp_start_job] @job_name = 'myJobToRunName'