SQL2005 SQLAgent MSDB security roles - by HMCSharon

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


12
0
Sign in
to vote
ID 156852 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 7/5/2006 9:09:20 AM
Access Restriction Public

Description

Want a user group to be able to build & execute jobs from Mgmt Studio without granting them SA.   Running SQL2005 RTM, i64 server, have tried setting SQLAgent roles under MSDB like SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole but none can modify existing jobs that they do not own.  Jobs need to be run by anyone in the large group, cannot seem to set owner as AD group name.
Sign in to post a comment.
Posted by boumerlin on 11/26/2014 at 7:50 AM
I know this is closed, but this really needs to be given serious consideration. There are valid reasons why you would want to give a group of users control over specific jobs, but not make them admins on the server. The simplest approach is to allow a group to own the job, thus giving all the users the rights they need to create and modify jobs, and the same group can control the resource access on the network to fileshares, etc.
Posted by RickGot on 1/29/2014 at 1:31 PM
OK MS folks, closing this leaves a DBA with 2 options. First, deny those that need to do agent job design and deployment the right to do so, since I certainly don't want to be granting them sa privleges. The second option is worse ... grant them sa privileges. Essentially, you've rendered SSIS useless to our organization. What this means to our organization ... we've got a group of people who will now have to learn powershell scripting who will now have to schedule execution of those scripts (run against sql servers) in Windows scheduler, or some third-party chron application. Thanks a lot. Oracle products are sounding better and better for our needs by the day.
Posted by Newfangled Old-fashioned Stuff on 6/12/2013 at 11:26 AM
So, how are we supposed to have a set of non-sysadmins that can share ownership of a set of jobs?
Posted by Tom Thomson on 8/10/2011 at 8:27 AM
The real flaw here seems to be that a group can't own a job. That seems to be a pretty silly restriction
Posted by gtsdba on 5/17/2011 at 2:03 PM
please reopen this. there is a need for groups of users to be able to manipulate jobs they have a shared responsibility for (stop\start\drop\recreate\edit). Currently this can only be done easily by granting sysadmin which is overkill. We need something between sqlagentoperatorrole and sysadmin. Perhaps a user defined job category and then users can be defined that are allowed full control of jobs in that category. without this developers are completely dependant on a DBA to manage jobs should the creator of the job be off work, this leads to pressure to grant sysadmin to developers in dev environments.
Posted by Michelle A. _ on 2/12/2007 at 12:15 PM
I think that this should be reopened. The SQLAgentOperatorRole should allow a domain account to create a job (with that account as the owner) and then manage and edit the job. Our domain accounts in this role are unable to edit jobs that they own. But, if we create a sql login and add it to this SQLAgentOperatorRole - it seems to work as designed.
Posted by Microsoft on 7/10/2006 at 11:07 PM
Thanks for the feedback

This is by design that only sysadmins can edit jobs that they do not own, for security reasons.

Thanks,
Gops Dwarak