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.
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.