New security role for SQL Agent - SQLAgentAdminRole
I feel the SQL Agent roles are too limiting. If you want to allow freedom in a development, or assign management of SQL Agent jobs to a person; even SQLAgentOperatorRole doesn't do much. This means SysAdmins are still required to assist others in altering/removing jobs.
I propose a new role that allows the member to do anything with SQL Agent, like a member of SysAdmin, but without the access to all other parts of SQL Server that SysAdmin provides.
Thanks for the suggestion. We are not currently planning to do any work on this, though.
Indeed it is very hard to manage SQL Agent jobs currently in bigger organization
A pity Microsoft just declinded this idea so fast. Hopefully this will change in the future
I'm wondering if Microsoft could update job permissions in the same way they treat SSIS packages. We can add groups or individual users to the folder level and then on the individual packages for SSIS.
It would be great if we could have something similar for SQL Agent jobs. E.g. a certain security group has the ability to edit and run job A, while another security group has the ability to edit and run job B.
With efforts to consolidate environments and prevent setting up 100 new instances we are running into issues constantly on shared tenant environments with one group of devs needing to update their jobs, but another group of devs not wanting anyone (and for good reason) not to have sysadmin.
Granular permissions like SSIS, or at least make it possible to make a security group the owner of a job. It's especially confusing because we *DO* have the ability to grant groups permissions on proxy accounts, but yet without sysadmin that same group can't modify their job, only the job owner.
Dan Carollo commented
Microsoft's response is unfortunate. You can already grant any INDIVIDUAL user account to own a job. It does not seem like a stretch to grant this to security GROUPS. We don't want to make individuals owners of jobs because that is an unmanagable security practice.
Al Howarth commented
Thanks Gerald! I was a little disappointed in the reply as well. No explanation why, or even if is was discussed. I think this feature could be desired by a majority of users. Especially as you state, those that are part of large organizations or any with government oversight.
Gerald Versluis commented
The comment shows a complete lack of understanding customer needs. We have users in our BI department that need permission to reschedule jobs, regardless of who owns them. However, they cannot be sysadmin because they should not be allowed access to all databases - the law forbids that (in this case, the GDPR). The way Microsoft has built the SQL Agent permissions, we will need to go to great lengths with external scheduling tools to accomplish simple tasks. Please reevaluate this decision.