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?