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

SQL Agent permissions model help Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 5:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
Hi there, I've been trying to get some security defined around SQL Agent to a point I'm happy with the controls in place but allowing my developers to do their work without too many restrictions (aka needing my intervention ). I've hit an obstacle and I'm not sure of how to get around it, please help!

I have three environments - DEV, UAT & PRD. I have granted my developers sysadmin on DEV, no worries there. Moving to UAT, I have granted them SQLAgentOperator role so they have the ability to edit / enable / execute jobs. In PRD, security is stripped back further in that a job needs to be owned by sa to run (i.e. if something gets loaded up, it won't run until I've reviewed it and no one can amend it either).

In UAT I have created a proxy account, permissions granted to a database role I've created in msdb, which my developers have been added to, that then allows them to run SSIS jobs (SQL Server doesn't allow you permission to run) by using the runas selection in the jobstep.

The issue is around UAT. We generally work in a team, so different people will be working on the same piece of work at different points. The SQLAgentOperator role only allows you to edit jobs in your own name. SQL Server won't allow you to add a group as a job owner and therefore I'm a little stuck, if user A has created a job, then user B wants to edit it, I still need to intervene in order to change the owner (since ownership change is reserved for sysadmin).

So how do I get around this? Is this by design? One way I've considered solving this is by granting their secondary ("admin") accounts sysadmin permissions, but ideally I'd like a neater solution.
Post #1462009
Posted Monday, June 17, 2013 8:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
Any help on this? Anyone come up with a way of getting round this? Or I'm just interested in what others do?
Post #1464157
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse