Permission to run only certain SQL jobs in 2008

Question

Permission to run only certain SQL jobs in 2008

Guras

SSCertifiable

Points: 6518
More actions
June 18, 2013 at 8:11 am

#279284

I have an AD group Group1 that needs access to the SQL jobs ( only certain ones) so that any users within that group can run/stop/view history of the job .
- The users in this group are not sysadmin
- None of the jobs are owned by the users.
Does SQLAgentUserRole solve this? How do I give access to specific jobs but not the all?
Any help would be greatly appreciated. Thanks

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

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

J Good Default port Points: 1435 More actions · Answer 1

You could create a stored procedure that executes the specific job with sp_start_job. If you add 'WITH EXECUTE AS YOUR_JOB_OWNER' to the stored procedure then the users will not need access to execute the jobs, only access to execute the stored procedure.

MSDN links:

sp_start_job - http://msdn.microsoft.com/en-us/library/ms186757.aspx

execute as - http://msdn.microsoft.com/en-us/library/ms188354.aspx

I'm sure there are other ways to do this, but this is just one possible solution.

Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]

red_serene SSC-Addicted Points: 433 More actions · Answer 2

SSC Rookie: I am afraid this advice will not work. In order to have permissions to run the sp_start_job command, the user would need to be granted one of the SQL Agent database roles - which would give permissions on all jobs, or at least on jobs owned by the user. This is specifically not what the original question desired. From BOL for sp_start_job :

----------

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

------------

Also, reference to SQLAgentUserRole in BOL reveals "Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. " As the users in this case do not own jobs, they should not be granted this role.

J Good Default port Points: 1435 More actions · Answer 3

Sorry, after i re-read my post I realized it wasn't very clear.

The Stored Procedure would need WITH EXECUTE AS SOME_USER where SOME_USER has the necessary level of access to run sp_start_job.

This post helps to clarify:

http://dba.stackexchange.com/questions/10449/grant-permissions-to-run-an-sql-server-job

Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 4

here's a classic example of what J Good is talking about;

the proc below runs under elevated priviledges, so i can grant a normal user EXECUTE ont eh procedure to allow specific, limited admin stuff

dbcc freeproccache requires ysadmin priviledges/

--create our super user

CREATE LOGIN [superman] WITH PASSWORD=N'NotARealPassword',

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;

GO

--make our special user a sysadmin

EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin';

GO

--noone will ever login with this, it's used for EXECUTE AS, so disable the login.

ALTER LOGIN [superman] DISABLE;

USE MSDB

--EXECUTE AS must be a USER, not a login, add our user

CREATE USER [superman] for LOGIN [superman]

GO

--the EXECUTE AS must be a user in the database...not a login

CREATE procedure pr_CallBoostedSecurityProcess

WITH EXECUTE AS 'superman'

AS

BEGIN

dbcc freeproccache

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!