sql sever agent jobs

Question

Post reply

sql sever agent jobs

ColeTrain

SSC Enthusiast

Points: 190
More actions
October 7, 2009 at 11:57 am

#215125

I have a mix between developers and application administrators that need access to view, and run defined sql agent jobs. I don't want to grant sysadmin rights to each user and only want a subset of user's to have rights run certain jobs not all jobs. Can I do this just through the sp_addrolemember SQLAgentOperatorRole for defined jobs? Can I create a group or a role that would have access to the defined jobs?
Thanks in advance.

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

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

91rghf Default port Points: 1403 More actions · Answer 1

Use the roles in the msdb database provided by Microsoft. These are just for this purpose.

ColeTrain SSC Enthusiast Points: 190 More actions · Answer 2

I can use the roles defined in the msdb database. What I would like to do is define what jobs can be run and by whom not granting rights to every job. Also is there a way to display everyone who has been granted rights for the SQLAgentOperatorRole

It would be nice to define a group of people for a subset of jobs.

Again Thanks for any assistance.

91rghf Default port Points: 1403 More actions · Answer 3

Ah, I missed the part about not *all* users. That part might be a little tricky. I imagine you could define your own roles and then within the job itself, validate the caller is actually a part of a specific role (i.e. IS_MEMBER function). I would do this on top of adding everyone to the appropriate SQL Agent role(s). Personally, I'm not a strong advocate of messing with permissions within the system databases but adding your own role should be pretty straight forward.

To identify who is part of the SQLAgentOperatorRole, there are several ways. The easiest way would be to use the sp_helprolemember procedure:

USE [msdb]; EXEC sp_helprolemember @rolename='SQLAgentOperatorRole';

91rghf Default port Points: 1403 More actions · Answer 4

Let me revise my original statement. It sounds like you can use the SQLAgentUserRole or SQLAgentReaderRole to limit running jobs that a user does not own. The trick would be to make the owner of the job as a specific user (not sure if this can be a domain group?). The SQLAgentOperatorRole is the most priviledged and can execute any job regardless of ownership. Check out the BOL for information on SQL Server Agent Fixed Database Roles.