Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Non-sysadmin needs ability to maintain SQL Agent Jobs


Non-sysadmin needs ability to maintain SQL Agent Jobs

Author
Message
Tom Van Harpen
Tom Van Harpen
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1056
We have a team member that does not have sysadmin but we would like to give this person the ability to setup and maintain SQL Agent jobs.

We've found that only a member of sysadmin can do this, unless of course this person owns the job. The jobs are all SSIS and all owned by the same SQL Account.

We tried to have them work with the jobs by logging in as this SQL account but then access to the file system is restricted. We've tried all the MSDB roles but nothing seems to works.

I'm just wondering if anyone knows what security settings would give someone the ability to modify Agent jobs? or if there is a work around?
SQL Show
SQL Show
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1078
Have you tried Proxy Accounts ?

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Tom Van Harpen
Tom Van Harpen
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1056
SQL Show (4/5/2013)
Have you tried Proxy Accounts ?

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


thanks but did you read my post?

We use proxy accounts to run jobs, what I'm looking for is a way to have someone create and maintain jobs without giving them sysadmin.
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
There is no simple way to do this without sysadmin I am afraid,, its a design flaw in SQL, but an intended one. There is a connect item about this, perhaps you would like to vote on it -

https://connect.microsoft.com/SQLServer/feedback/details/156852/sql2005-sqlagent-msdb-security-roles#tabs/

what I have done in the past is set up a SQL account to be used by a group of people and they had to use this account to maintain their jobs. the exact rights you would need to give it would depend on what the jobs did.

dbcreator role possibly

EXECUTE on master.dbo.xp_sqlagent_enum_jobs
add the user to msdb
add to role SQLAgentOperatorRole
grant the account rights to use the proxy (execute sp_grant_login_to_proxy)

grant select on msdb.dbo.sysproxies
grant SELECT on msdb.dbo.sysjobs
grant select on msdb.dbo.sysjobs_view
grant select on msdb.dbo.syscategories

this was in a dev environment rather than production

---------------------------------------------------------------------
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2162 Visits: 487
This works for us:

1. User must exist in the msdb database.
2. User requires one of 3 roles in msdb database: SQLAgentOperator, SQLAgentReader, SQLAgentUser role.
3. Make sure the principals are mapped to the proxy.

The one "gotcha": Never let a non-admin explicity name a job owner as only admins have the permission to do that.

If you are interested, we wrap the actions in a stored procedure that does the following:
1. Dynamically creates a new job name
2. Executes msdb..sp_add_job
3. Executes msdb..sp_add_jobstep (make sure you specify the @proxy_name parameter!)
4. Executes msdb..sp_add_jobserver
5. Executes msdb..sp_add_jobschedule


I may be missing something, but this should put you on the right track. See books online for privileges required to run the stored procedures starting with: msdn.microsoft.com/en-us/library/ms182079.aspx (sp_add_job).

Steve Neumersky
Tom Van Harpen
Tom Van Harpen
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1056
Thanks for the feedback we'll take a look these ideas - Tom
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2162 Visits: 487
No problem.

In our solution we create jobs on the fly and set them to run one second after scheduling the job. By doing this you can still allow the SQL Server Agent service account to execute the jobs instead of getting cute with security assignments Smile
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1958
sneumersky's solution using MSDB Roles works partially, because it does allow users to be able to run, edit or create Agent jobs without being a sysadmin, but it has limitations.

1) The groups allow the users the described rights to ALL Agent jobs. I'd prefer to be able to give users the rights to manipulate some jobs but not others. For example, they should be able to run the backup job on certain databases whenever they want before directly editing the data in them, but I'd prefer they not be able to run Agent jobs on databases they don't deal with.

2) This can be done in some instances by making the user the Owner of the job, but in some cases we have more than one user we'd like to give those rights to. I thought I could do this with an AD group, but you can't make an AD Group an object owner, nor can you give an agent job two owners.

So what we have now is a situation where the users can access the Agent jobs they need, but we have to trust them not to mess with other jobs on the same instance.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2162 Visits: 487
Unfortunately, this is about as far as I could tip-toe the fine line between security and flexibility......unless this challenge could be solved using a custom "security table" and driving the solution that way. I have never tried that before.

I would follow George's guidance on connecting with MS regarding this issue and seeing if they could provide a fix or a workaround Smile

Sorry I could not be of more help Sad
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 920
Tom Van Harpen (4/4/2013)
We have a team member that does not have sysadmin but we would like to give this person the ability to setup and maintain SQL Agent jobs.

We've found that only a member of sysadmin can do this, unless of course this person owns the job. The jobs are all SSIS and all owned by the same SQL Account.

We tried to have them work with the jobs by logging in as this SQL account but then access to the file system is restricted. We've tried all the MSDB roles but nothing seems to works.

I'm just wondering if anyone knows what security settings would give someone the ability to modify Agent jobs? or if there is a work around?


Seeing as all agent jobs are maintained in the msdb database, it makes sense to create a user with access to the database. This link http://msdn.microsoft.com/en-us/library/ms187901.aspx explains how.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search