Question: I have different developers working on a SQL Server Instance and require to grant them flexibility to manage all SQL Agent jobs , but not grant them sysadmin privileges. Is this possible?
Answer: Unfortunately, SQL Agent job full administration can only be completed by sysadmin role members. There are the Agent roles – User, Reader, Operator - found on the msdb database, but despite the flexibility - a logon without sysadmin can only manage jobs owned by their logon.Read more on SQL Server agent fixed roles - SQL Server DBA
If the logon attempts to edit a job not owned by them – a Microsoft SQL Server Error:14525 appears - Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
1) Create a group with sysadmin privileges – add the logon for a temporary time to administer the SQL Server agent jobs. There are a number of tools available such as the security management tool - Varonis
2) Add permissions to a number of relevant views and tables in msdb. I’ve seen DBAs manage the permissions with this method – but unless there is a persuasive need – I find this method to messy. Unless it’s documented and scripted , it can cause problems in DR and recovery