Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs.

Given that it seems that MS doesn't seem to be budging on granting rights to windows groups, I was wondering if anyone had a creative workaround? I can't let them have the system wide agent roles. http://connect.microsoft.com/SQLServer/feedback/details/508137/allow-windows-groups-to-own-sql-agent-jobs

I'm about to create a user and tell these two to log in to a shared account if they want to run/edit their jobs - but then there is no accountability.

Anyone have a creative solution I have missed?

Thanks :)

share|improve this question

1 Answer 1

up vote 3 down vote accepted

In the past to solve this I've created a web based app to allow users to change jobs. The app connected via a sysadmin login, but only allowed the user to edit/start/stop the jobs that they had access to via the app.

Another option would be to go the single login route, but have auditing enabled for that login specifically and make sure that the auditing tracks the computer name as well as the login. That way if someone does something stupid you've got an audit trail and it'll tie back to their computer.

Group's can't be used to own jobs as the job runs under the context of the job owner.

There does need to be a better security model for jobs however.

share|improve this answer
2  
+1 but the audit route only works if they access the server remotely, as opposed to RDP in and then connect locally. –  Aaron Bertrand Mar 5 '13 at 19:06
    
mrdenny - I think the web based app would be good but beyond my limits at the moment. As a long term solution I like that though. @AaronBertrand - I am fighting RDP access but I have a feeling I might lose that battle. I figured I could add some auditing overhead but if they get RDP access I'm leaning toward just saying "If they don't want to go through the DBA, then I am not responsible for their antics." I don't want them playing on the cluster. –  Cate Donoghue Mar 5 '13 at 19:13
1  
Easy solution to the RDP problem. Uninstall Management Studio from the production console. –  mrdenny Mar 5 '13 at 19:14
2  
@mrdenny ha! If only it were that simple. In a lot of environments where you have to VPN to one location just to connect remotely to the data center, crossing one or more domains in the process, sometimes having Management Studio on the server is a necessity. It's not my preference sometimes that is pretty high up on the political priority list. –  Aaron Bertrand Mar 5 '13 at 19:36
1  
True. I "try" and get people to setup an admin VM for SSMS, etc on the same domain as the SQL Server, or at least that's trusted enough to that domain that I can runas SSMS from that machine. It's my person dream that no one will ever have to run SSMS on the console. But I know that it's a pipe dream. –  mrdenny Mar 5 '13 at 20:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.