SQL Server Home
SQL2005 SQLAgent MSDB security roles
as By Design
7/5/2006 9:09:20 AM
User(s) can reproduce this bug
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.
SQL Server 2005 - Enterprise Edition (IA64)
Windows Server 2003
Operating System Language
Steps to Reproduce
In MSDB security, checked boxes for SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole for my test domain "TestUser" account.
Cannot modify an existing job created by other user. Can create a new job.
Looking for solution to allow users to modify jobs in SQLAgent admin type role without granting them SA to all of SQL Server
to post a comment.
Please enter a comment.
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.
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?
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
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.
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.
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.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft