Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Non-sysadmin needs ability to maintain SQL Agent Jobs Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 8:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:55 AM
Points: 400, Visits: 947
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?
Post #1438841
Posted Friday, April 5, 2013 5:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Have you tried Proxy Accounts ?

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Post #1439164
Posted Friday, April 5, 2013 8:13 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:55 AM
Points: 400, Visits: 947
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.
Post #1439241
Posted Friday, April 5, 2013 9:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 5,976, Visits: 12,887
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


---------------------------------------------------------------------

Post #1439272
Posted Friday, April 5, 2013 9:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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
Post #1439295
Posted Friday, April 5, 2013 9:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:55 AM
Points: 400, Visits: 947
Thanks for the feedback we'll take a look these ideas - Tom
Post #1439316
Posted Friday, April 5, 2013 9:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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 :)
Post #1439322
Posted Friday, May 17, 2013 11:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:07 PM
Points: 529, Visits: 1,569
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.
Post #1454111
Posted Monday, May 20, 2013 8:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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 :)

Sorry I could not be of more help
Post #1454547
Posted Tuesday, June 25, 2013 7:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 308, Visits: 476
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.
Post #1467140
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse