What would you do if you were asked to grant a few users the permission for altering SQL Jobs? It is a tricky task to carry out although many of us would think that granting SQLAgentOperatorRole role might be enough, however, many years ago no sooner had I done it than I realised it did not work as expected, and now I think most of us faced up this issue at first.
The SQLAgentOperatorRole role allows user to alter SQL jobs as long as the user is the owner of the SQL job, otherwise, the user need to be SysAdmin at SQL instance level. What’s more, one of the best security practices says that nobody but DBA must be SysAdmin, and we should use Windows Authentication. Nevertheless, when it comes to owners for SQL jobs they should use 'sa' as owner which does not mean having enable that account, it should be disable. In this sense, it is highly advisable to have the disabled account “sa” as the owner of all SQL jobs and avoid granting SysAdmin privilege. So, it is of paramount importance not to use a windows user as owner of a SQL job because SQL Server will always validate windows users against the Active Directory and it is likely to get unforeseen errors during that process.
Today I am going to share with you a stored procedure to enable users to alter SQL Jobs without the need of granting SysAdmin privilege. This stored procedure consists of a logic that will allow a specific user to take the ownership of a SQL Job so that the user can be able to alter it and after making the changes the user can change the ownership to ‘sa’ (or the original owner). This is the stored procedure that I mentioned above.
USE [msdb]
GO
CREATE PROC[dbo].[usp_change_owner_job] @jobname varchar(max), @newowner varchar(max)
with execute as owner
as
begin
declare @old_owner varchar(max)
select @old_owner= s.name from msdb.dbo.sysjobs j inner join sys.server_principals s
on j.owner_sid= s.sid where j.name=@jobname
--declare @newowner varchar(max)
--set @newowner=ORIGINAL_LOGIN()
EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newowner
--print @newowner
declare @msg varchar(max)
set @msg= 'The owner of ' +@jobname + ' job was changed from ''' + @old_owner + ''' to ''' + @newowner + ''''
print @msg
end
So, for instance, if you have a SQL job whose owner is 'sa' and your user is 'User2', you will not be able to alter the job until you take the ownership of it temporarily. Here are all the steps you must follow:
1. Create the stored procedure 'usp_change_owner_job' based on the code above.
2. Grant EXECUTE permission on 'usp_change_owner_job' to 'User2'.
3. Grant SQLAgentOperatorRole role to 'User2'.
4. Change the ownership of the SQL Job to 'User2' by using the stored procedure 'usp_change_owner_job'.
EXEC msdb.dbo.[usp_change_owner_job] 'BusinessJob01', 'User2'
The owner of BusinessJob01 job was changed from 'sa' to 'User2'
5. Now the User2 has the ownership of the SQL job and is now able to alter it.
6. After making the changes on the SQL Job, the 'User2' must change the ownership to 'sa'.
The owner of BusinessJob01 job was changed from 'User2' to 'sa'
The owner of BusinessJob01 job was changed from 'User2' to 'sa'
Having successfully completed all the steps will you be able to alter any SQL Job without granting SysAdmin privilege to users. That is all for now. Let me know any remarks you may have.