June 11, 2013 at 5:38 am
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 :-D). 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.
June 17, 2013 at 8:03 am
Any help on this? Anyone come up with a way of getting round this? Or I'm just interested in what others do?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply