SQL 2012 Grant execute rights to user on specific SQL Agent Job

Question

SQL 2012 Grant execute rights to user on specific SQL Agent Job

PSB

SSChampion

Points: 13195
More actions
August 15, 2016 at 5:41 am

#400576

Hi,
I have to allow a business user to run a specific SQL Agent job. I wanted the user to run a procedure from msdb to strat the job instead of having explicit rights to run from SQL Server Agent Jobs.
For this purpose,I have created a role in the msdb database using the following script :
USE [msdb]
GO
CREATE USER [JobA] FOR LOGIN [DIR\SMini] WITH DEFAULT_SCHEMA=[dbo]
GO
-Then I created the procedure which she can run to start the job and granted execute rights to the role to execute this procedure.
-- Create SQL Server Agent job start stored procedure with input parameter
USE msdb
GO
ALTER PROC dbo.usp_StartJob
@MyJobName sysname = 'My Job Name'
AS
DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)
EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@MyJobName;
RETURN (@ReturnCode)
GO
Also, to her login, I have granted SQLAgentUSerRole for msdb database.
Still she receives an error saying :
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('My Job Name') does not exist.
Please help.
Thanks,
PSB

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

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

You can try adding execute as owner while creating the procedure, so that the user can impersonate your permissions.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

PSB SSChampion Points: 13195 More actions · Answer 2

Ok, I will try that. Do I still need to grant SQLAgentUSerRole to her login for msdb database ?

Thanks,

PSB

joeroshan SSChampion Points: 10377 More actions · Answer 3

Since you are using the stored procedure route, they do not need the fixed database role. But you need to give explicitly grant execute permission to the procedure you cerated

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

joeroshan (8/15/2016)
You can try adding execute as owner while creating the procedure, so that the user can impersonate your permissions.

To run the job, probably.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)