Manage SQL Agent jobs without sysadmin

24 June,2013 by Jack Vamvas

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.

Workarounds 

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

 

Read More

 

SQL Server agent fixed roles - SQL Server DBA

SQL Server Agent Job Steps - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Manage SQL Agent jobs without sysadmin


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer