Grant execute to ONLY ONE SQL agent job to user

Question

Grant execute to ONLY ONE SQL agent job to user

PSB

SSChampion

Points: 13195
More actions
July 18, 2016 at 9:09 am

#315247

Hi,
How do I grant access to a user who is not a user from 'sa' group to execute just one job : Job1 ? I have used these commands below but now the user has access to execute/view all jobs. How do I limit to just one that I need him to view and execute.
USE [msdb]
GO
CREATE USER [ExecuteTransformationJob] FOR LOGIN [DIR\User1]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'ExecuteTransformationJob'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'ExecuteTransformationJob'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'ExecuteTransformationJob'
GO
Thanks,
PSB

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You can't easily do this. Jobs are at the row level.

What you could do is create your own procedures in msdb (script and save in a VCS for DR purposes) that read this job, or execute this job, and then grant a role the rights for your procs, adding the user to a role.

PSB SSChampion Points: 13195 More actions · Answer 2

Can you provide me with a sample procedure or some helpful links ?

PSB SSChampion Points: 13195 More actions · Answer 3

I assigned the user permission to start job .

How does he know when it completes ?

ALTER PROC usp_StartTransformationJob

@MyJobName sysname = 'Transformation'

AS

DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)

EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@MyJobName;

RETURN (@ReturnCode)

GO

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

OK, sorry, was busy with other work.

First, if you're doing this for users, you're going to find yourself in a bind at some point. Users leave companies, and new users come.

So, set up a role first, and a group in Exchange/email, for notifications.

I'd say you are on the right track for this with your code below. Build a procs that can do these things:

- start job

- query job

- stop job

Grant rights to a role to execute your procs and add the user to a role. You can add notifications to a job step or to the job (I think, don't have a 2005 instance handy) that send a note to a group in your email system (or an alias). Map that to your user.