May 16, 2011 at 9:59 am
Has anyone ever found a way to give users the ability to create\drop\execute agent jobs owned by people other than themselves without giving them sysadmin rights?
---------------------------------------------------------------------
May 16, 2011 at 11:32 am
In SQL Server 2008, the SQLAgentOperator role can, but they must do so via use of stored procedures.
K. Brian Kelley
@kbriankelley
May 16, 2011 at 11:35 am
I think Brian might be right. But the bigger question is why do you want to let them do this without your intervention?
CEWII
May 16, 2011 at 2:14 pm
Thanks for the replies.
Brian, can you expand on that, do you mean they could run sp_delete_job? BOL does not seem to suggest that.
Elliot, this is a dev environment and the devs have built processes that drop and recreate jobs then execute them when they do a release. They want to be able to do that no matter who the job owner is. In their old SQL2005 dev environment they used to have sysadmin so they got away with these practices. In their new SQL2008 dev environment I don't want them to have sysadmin (for reasons of SOX and my sanity) so now only the person who created the job can test their releases. In reality they just want the freedom of sysadmin back and this is their argument for being granted it.
The jobs also run SSIS packages via a proxy so that has sysadmin issues as well. I have come up with this so far.
A SQL account that they share for release testing. This account will own the jobs.
the account has rights
SQLAgentOPerator
DBCreator
Owner in any databases the jobs run TSQL in (the process creates new databases so it will own those)
sp_grant_login_to_proxy for the SSIS proxy --edited
all I have left to check is that backups can be taken via the jobs (thats another step in the process)
---------------------------------------------------------------------
May 16, 2011 at 3:14 pm
I seemed to remember that the SQLAgentOperator should allow them do access other's jobs. Because normally they would have access to theirs anyway.
As far as SOX, it rarely ever applies in dev environments, sanity on the other hand....
CEWII
May 17, 2011 at 7:27 am
Books Online: SQL Server Agent Fixed Database Roles
Covers roles, permissions, etc. From that page:
3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
4 SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
K. Brian Kelley
@kbriankelley
May 17, 2011 at 7:37 am
thankyou brian but they require to do more than disable\enable. they would want to amend the contents of job steps.
I am going to go with the setup described above, but I bet they will still be looking for sysadmin for some reason.
Maybe there is a requirement for a role that gives full rights to SQLAgent jobs (or certain job categories you can define) but not full blown sysadmin
---------------------------------------------------------------------
May 17, 2011 at 9:28 am
Search connect.microsoft.com for a submitted request, because likely there is one. The more folks that indicate that a feature is wanted or a bug is a problem, the more likely the SQL Server team will look at it.
K. Brian Kelley
@kbriankelley
May 17, 2011 at 3:24 pm
there is one for SQL2005, but it has been closed as 'by design'. I have requested it be reopened
please vote!
---------------------------------------------------------------------
June 1, 2011 at 5:33 am
this worked
use msdb
EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole',@membername = 'myuser'
/*
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
*/
June 1, 2011 at 5:50 am
makarov (6/1/2011)
this workeduse msdb
EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole',@membername = 'myuser'
/*
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
*/
I'm afraid that won't give full control over other peoples jobs.
I have a working setup as described in my earlier post plus grant db_datareader in msdb. This does require the set up of a SQL user ID though which everyone who wants to share jobs has to log onto SQL with for the purpose of manipulating the jobs. Not perfect but workable especially if jobs are divided into discreet sets by application or function.
Please vote for the connect item.
---------------------------------------------------------------------
February 3, 2012 at 5:23 pm
Hello, I would like to give users full job control, create, delete, change. How can I dio that. I want any user to change jobs not belonging to them and to be able to switch job ownership.
Does anyone have any ideas?
Thanks,
Michael
February 6, 2012 at 2:42 am
Only sysadmin will give them that level of control.
Otherwise set up a SQL account, have all jobs owned by that account and get everyone to log on with that account when working with jobs.
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply