March 16, 2015 at 10:58 am
I am desperately trying NOT to give a windows group of developers sysadmin rights, but the one stumbling block is that they need to be able to set up, schedule and run and be able to modify sql agent jobs running their ssis packages.
Having got a test login in that windows group I've tried a lot of things and all I can get when I execute a sql agent job is The job failed. Unable to determine if the owner (domain\testaccount) of job Test2 has server access (reason: Could not obtain information about Windows NT group/user 'domain\testaccount', error code 0x5. [SQLSTATE 42000] (Error 15404)).
The sql agent service account is a domain account with sysadmin permissions. The ssis jobs are run by a proxy account with sysadmin permissions and windows server permissions. Both those accounts and the windows group which includes domain\testaccount has dbo database permissions on msdb including ssisadmin and sql agent operator roles and the actual ssis packages are saved to msdb with "Rely on server storage..." access permissions.
The windows group is also a member of the local Distributed COM Users group on the server.
I want job ownership to stay with the user so they can modify the jobs and schedules but they get the above error. If ownership is changed to sa the jobs run but only sysadmins can then edit them so I'd have to make the developers sysadmins which is what I want to avoid.
What am I missing here?
March 16, 2015 at 11:06 am
Probably not useful but the standard approach I've always used is to this is to give developers the access they need in a Dev environment and when they can show the code is tested, you get to migrate those changes to Production. Changes to jobs and schedules [in production] should in my opionion always be the db admins job supported by a solid change management process where users and owners approve the change.
March 16, 2015 at 4:23 pm
I would agree with Roger. The developers need a sandbox environment where they can do whatever they need. Particularly, they need to be able to set up the jobs they are intended to run in production. Else there can be hiccups once when things to live.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 18, 2015 at 2:29 am
Thanks - they've got their sandbox and got sa on that. Normally I would insist we migrate to live, but there's politics and history to this lot from companies amalgamating and their manager outranking the IT manager and thinking we're trying to take their jobs away!
This group also came out with a classic line when they were told that they should be developing on their new dev server (sql 2008) instead of on the live (sql 2005) - "OK, when we've finished building everything on live, we'll copy it to the development server to keep you happy" :crazy:
So I'd like to rein things in but this jobs issue is the final sticking point. They have some complex ssis data import jobs so we'd rather they could manage the jobs.
March 18, 2015 at 12:53 pm
One part solution could be to give temp access just for the duration of the update. Grant SYSADMIN on request for short period for them to do the updates and then remove it. From an audit/security perspective you could run a server side trace for the time its enabled so you can prove nothing was done that should not be done.
Using a trace might give you enough information to create a role/permissions but I suspect you have tried that.
This is a common polical football. So that getting stressed about it the approach I've used in the past is to clearly write up the risks and present that to management. It should then be their decision to accept the risk (in writing) and grant the access or deny the access. As a DBA's you can provide some options for them like the temporary access but ultimately they need to understand the risks and accept the risk. In a regulated environment like financial services the best a dev can hope for is temporary access based on specific request or issue.
March 18, 2015 at 2:44 pm
Look into these MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
I've listed them from highest permissions to lowest - if you grant one permission, the one(s) under it are automatically granted.
Those will allow a user to create/delete/modify/run jobs. Which permissions you grant will determine exactly what they can do.
-SQLBill
March 19, 2015 at 2:08 am
SQLBill - as I said above, they already have sql agent operator role (the highest level). Without sysadmin permissions it doesn't do the job.
March 24, 2015 at 8:33 am
P Jones (3/19/2015)
SQLBill - as I said above, they already have sql agent operator role (the highest level). Without sysadmin permissions it doesn't do the job.
Have you tried looked at using the Credential/Proxy method, this way they can keep their Operator role, while being able to run the job under an alias.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 4, 2015 at 9:04 am
Hi did you ever get a solution to this. I have exactly the same problem and scenario
I want the Developer to be able to schedule their own jobs, but unfortunately the domain account under which SQL Server and SQl agent run down seem to be able to determine whether they as job owners have permissions. with exactly the same error message as above
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply