Edit sql agent job if you aren't the owner or sysadmin

In a previous job I had as a Database Administrator III, our team was responsible for creating and maintaining SQL agent jobs that ran various database maintenance tasks and we would often use the standard maintenance plan wizard that comes with SSMS.

The wizard would provide you with an easy way to create simple common database maintenance jobs to keep your database healthy and happy.  Such tasks were things like the database check integrity task or the reorganize index task.

A common issue we would come across while maintaining these jobs had to do with the ability to edit the job if you weren’t the owner.  It was standard for us to create the job and use a common dba account so that all the dbas could edit the job but from time to time a dba would create the job using their own personal AD account and not the standard dba.  This didn’t cause too much inconvenience for us because we, as DBAs, also had the sysadmin privs so we could edit the job anyway.  But this got me thinking.  Was there a way to allow someone who wasn’t an owner of a job the ability to modify the job without granting them too much privilege like giving them sysadmin?

I came across a great article by Boris Hristov that explains a simple but effective method for doing so.

Any OOP programmer is familiar with the concept of overriding a method.  This is basically the same idea but overriding a system stored proc with a custom stored proc with one line of code that allows to achieve our goal.

Currently, if you try to modify a sql agent job a stored procedure called sp_update_job is executed.  This example is from MSDN.

USE msdb ;

GO

EXEC dbo.sp_update_job

    @job_name = N'NightlyBackups',

    @new_name = N'NightlyBackups -- Disabled',

    @description = N'Nightly backups disabled during server migration.',

    @enabled = 0 ;

GO

However, only members of sysadmin are able to edit jobs that they do not own.

If you create a copy of the dbo.sp_update_job as dbo.sp_update_job_override but add an ‘Grant Execute On’ at the end you can override it.

CREATE PROC [dbo].[sp_update_job_override]       @job_id                       UNIQUEIDENTIFIER = NULL,       @job_name                     sysname          = NULL,       @new_name                     sysname          = NULL,       @enabled                      TINYINT          = NULL,       @description                  NVARCHAR(512)    = NULL,       @start_step_id                INT              = NULL,       @category_name                sysname          = NULL,       @owner_login_name             sysname          = NULL,       @notify_level_eventlog        INT              = NULL,       @notify_level_email           INT              = NULL,       @notify_level_netsend         INT              = NULL,       @notify_level_page            INT              = NULL,       @notify_email_operator_name   sysname          = NULL,       @notify_netsend_operator_name sysname          = NULL,       @notify_page_operator_name    sysname          = NULL,       @delete_level                 INT              = NULL,       @automatic_post               BIT              = 1 WITH EXECUTE AS OWNER AS BEGIN    EXEC dbo.sp_update_job       @job_id       ,@job_name       ,@new_name       ,@enabled       ,@description       ,@start_step_id       ,@category_name       ,@owner_login_name       ,@notify_level_eventlog       ,@notify_level_email       ,@notify_level_netsend       ,@notify_level_page       ,@notify_email_operator_name       ,@notify_netsend_operator_name       ,@notify_page_operator_name       ,@delete_level       ,@automatic_post END GO GRANT EXECUTE ON [dbo].[sp_update_job_override] TO [SQLAgentOperatorRole] GO

So if you grant the user the SQLAGENTOPERATORROLE they will be able to make this call and it will complete successfully.

USE msdb ;

GO

EXEC dbo.sp_update_job_override

    @job_name = N'NightlyBackups',

    @new_name = N'NightlyBackups -- Disabled',

    @description = N'Nightly backups disabled during server migration.',

    @enabled = 0 ;

GO

Thanks to Boris and feel free to share this concept.

Thanks for reading,
MSSQL Canuck