SQL Clone
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1115
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1115
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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25672 Visits: 13701
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3256 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1115
Thanks for the feedback we'll take a look these ideas - Tom
sneumersky
sneumersky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3256 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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3753 Visits: 2000
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3256 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4633 Visits: 1025
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