Modifying not owned SQL Agent Jobs without being a sysadmin?

Failed to Modify SQL Agent Job 300x112 Modifying not owned SQL Agent Jobs without being a sysadmin?

Recently a colleague of mine from the application team asked me the following question:

Can we grant someone who is not a sysadmin such rights that he will be able to modify SQL Agent jobs that he does not own?

As probably many of you that work with SQL Server on a daily basis know this is not that straight forward to achieve, right? Even if you grant that user the SQLAgentOperatorRole it will not work, because we’re talking about jobs that this user does not own not the ones that he has created himself. That captured our attention and actually as you probably have already guessed – we figured it out! Let’s talk code now… 

Let’s create a login to play with, create a user for him in msdb and grant him the SQLAgentOperatorRole.

CREATE LOGIN [JobModifier] WITH PASSWORD=N'P@$$w0rd',
DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE msdb
GO
CREATE USER [JobModifier] FOR LOGIN [JobModifier]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'JobModifier'
GO

If you try(and I encourage everyone who haven’t tested this yet) to modify a job that this user does not own you will get the error from the photo above. Still, here is the piece of code you can use to try it for yourself:

EXECUTE AS LOGIN='JobModifier';
EXEC dbo.sp_update_job
      @job_name = N'YourJobName',
      @new_name = N'YourJobName_Modified';
-- REVERT;
GO

What you will see is this error:

Msg 14525, Level 16, State 1, Procedure sp_update_job, Line 145
Only members of sysadmin role are allowed to update or delete jobs owned by a different login.

Now how many of you remember their university years or their programming courses? Do you remember the concept of Method Overriding because this is exactly what we are going to do here. What we figured out we can do is to override the stored procedure that is actually being executed when a job is modified – dbo.sp_update_job located in msdb. Our goal is to override this stored proc and then grant the SQLAgentOperatorRole an EXECUTE permission to our newly created procedure. Let’s try that!

CREATE PROC [dbo].[sp_update_job_for_non_admins]
      @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_for_non_admins] TO [SQLAgentOperatorRole]
GO

So now my JobModifier user(clever name,huh?) can probably use this newly created stored procedure and update a job that he does not own? Let’s try!

USE msdb
GO
EXEC dbo.sp_update_job_for_non_admins
      @job_name = N'syspolicy_purge_history',
      @new_name = N'syspolicy_purge_history_disabled',  -- renaming the job
      @enabled = 0 ; -- disabling it also
GO

As you can see – “Command(s) completed successfully!”. However, you are not going to be able to accomplish the same by using the GUI itself. The job’s name will remain grayed-out for you, but who cares when you can modify it this way, right?

To sum it up for you – we have just build a simple solution that allows you to limit the users granted with sysadmin role even more and these are great news if you are a DBA, huh? So if any of your users were granted with the sysadmin role just because they need to be able to modify jobs that they don’t own, now is the time for you to start considering this solution and limit their rights!

P.S. The credits for building this script needs to go mainly to Miroslav Priesol! Thanks, mate! That’s what I call good work between a developer and a DBA! icon wink Modifying not owned SQL Agent Jobs without being a sysadmin?

Share on...

    Share This

    One Comment on “Modifying not owned SQL Agent Jobs without being a sysadmin?

    1. Pingback: (SFTW) SQL Server Links 03/01/14 • John Sansom

    What do you think?