I don’t know about your experience when it comes to SQL Server Agent jobs but often I receive requests to grant permission so that clients can handle their jobs in an autonomous way.
Any problem with that?
If you’re not familiarized with the difficulty this can bring, let me share with you a different way to accomplish the task without adding the login to the sysadmin instance role. I’m sure that you don’t want to do that on a production instance.
The next possible way is to make the login as the owner of the job and it needs to belong to one of the msdb fixed database roles! You can take a look at this documentation and see the matrix there for each role.
This means that if we are talking about a single login, you won’t have any problem at all.
Using service accounts instead
It is common having services accounts that are job owners so they can run within the proper context.
In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the service account needs permissions on agent roles and someone will need to connect to SQL Server using the service account (run as) so they can manage the agent job. It works, but not practical.
Many of the scenarios I get from clients is that they have an AD Group that contains all of all maintainers or support people.
Being able to put groups as job owner would be awesome, but unfortunately, it’s not possible.
I can hear the sad trombone!
My suggestion/recommendation/approach to this:
This may seem to be too much work, but at the end of the day, I feel it’s the best balance between security and the ability for the client to manage their agent jobs as they wish within their context.
I suggest that you create a wrapper for each system stored procedures that client need within msdb. It can be just the sp_update_job_step. You may also get a request to be able to change the schedules of the job and you need to create another wrapper also for the sp_update_job_schedule system stored procedure.
Use the EXECUTE AS OWNER so they can impersonate the sysadmins permissions and call the system procedure.
Be more granular (HIGHLY RECOMMENDED!!)
Say that you have your administration jobs and the jobs from the client. To narrow down the scope for the client you may want to add an extra validation using the job name prefix.
If they don’t have a naming convention, you can always ask if they can start using one and update the agent jobs accordingly.
Ask them to help you to help them!
The base principle here is simple if the name starts with the specified prefix the execution can proceed, otherwise, it will stop running and will return an error message saying they can’t update that specific job.
Here is a code example of a wrapper for the
sp_update_job system stored procedure:
USE [msdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[sp_update_job_for_non_admins] @job_id UNIQUEIDENTIFIER = NULL, -- Must provide this or current_name @job_name sysname = NULL, -- Must provide this or job_id @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 -- Flag for SEM use only WITH EXECUTE AS OWNER AS BEGIN IF EXISTS (SELECT [name] FROM msdb.dbo.sysjobs WHERE job_id = @job_id AND [name] LIKE '<customer prefix>%' ) BEGIN EXEC msdb.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 ELSE BEGIN RAISERROR ('The job_id used does not belong to an <customer prefix> job.', 16, 1); END END GO
More examples on my GitHub
If you want to leverage on the stored procedures that I have already created you can download them from ManageAgentJobsNonsysAdmin folder on my GitHub repository
Giving permissions to the wrapper objects
Create a role (will be easy to manage) on the msdb database and add the logins (nominal or groups) to it.
Grant EXECUTE permissions for all of your compiled *_for_non_admins stored procedures.
Other options are to grant the EXECUTE permissions to the existing database fixed role where the client login/group is already member.
T-SQL code example:
use [msdb] GO GRANT EXECUTE ON [dbo].[sp_update_job_for_non_admins] TO [SQLAgentOperatorRole] GO
You can be more creative but be aware of the maintenance costs
Another option that comes into my mind is to include the agent jobs names (totally or partially) on a control table.
However, this can bring more work when talking about maintaining this process. If the client has somehow a fixed number of jobs maybe it is not too much work otherwise it can be a nightmare.
We have seen how we can provide more control to clients so they can manage their jobs without compromising security.
I have used this method several times with success. By success I mean, I was able to explain to the client the limitation on SQL Server side and, on the other hand I present to them a possible solution.
It is not easy to change the code within a step using the stored procedure instead of using the interface? Sure it is not! But at least you provide a working solution that makes the client autonomous! In order to help them show how they can script out the changes, add the suffix for NonAdmins and they are good to go.
Thanks for reading.