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! 😉