UNDERCOVER TOOLBOX: Sp_ChangeJobOwnerShip – Changing Ownership in bulk.



How many times have you seen SQL Agent jobs with random ownership? It can be very frustrating when you are trying to keep things standardised and new agent job creations are not being set to an owner which is part of your Standardisation.

There are times when you may want to have certain jobs have an owner that differs, it all depends how you like to run your show.

Ever had a Job start failing due to a Job owner that no longer exists?

That is just frustrating! and completely unnecessary unless you planned it that way of course 🙂 maybe you intended for the job to start failing when that job owner left the business? in that case High Five!  highfive.gif


Whether you are keeping on top of Job Ownerships, maintaining or even starting out with a Job ownership Standardisation plan this procedure is going to save you a lot of time.

This Stored Procedure is similar to sp_ChangeDatabaseOwnership , in fact so similar that it shares most of the code except it conducts its checks against SQL Server Agent Jobs.

Here is how it works , Not interested in all of the jargon? then feel free to skip to the bottom for the code 🙂

check all Agent Job ownership’s for a specific Login, lets assume SA – when this checks the Jobs if it finds a Job where the SID does not match the login passed in then it will show the results and include two statements:

  • An sp_update_job statement for the Agent Job and Job Owner you have specified
  • An sp_update_job statement for the Agent Job and Current Job Owner so that you can revert if necessary


EXEC sp_ChangeJobOwnerShip 'SA'

2017-09-22 11_07_35


Column: ChangeToNewOwner

EXEC msdb.dbo.sp_update_job @job_name=N'MaintenancePlan.Subplan_1', @owner_login_name=N'SA';


Column: RevertToOriginalOwner

EXEC msdb.dbo.sp_update_job @job_name=N'MaintenancePlan.Subplan_1', @owner_login_name=N'SQLUNDERCOVER\SQLUndercoverUSR01';



If we pass in a Login that does not exist an error will appear as below:

2017-09-22 11_08_57.png



Here is the code:

USE [master]

--Procedure Name: sp_ChangeJobOwnerShip
--Author: Adrian Buckman
--Create Date: 22/09/2017

--Description: Produce a script that will provide ALTER statements to change the Agent Job
--ownerships to the new owner and also ALTER statements to revert back to the old owner

--Revision History:


CREATE PROCEDURE [dbo].[sp_ChangeJobOwnerShip]
(@JobOwner NVARCHAR(128) = NULL,
@Help     BIT           = 0
IF @Help = 1
PRINT 'Parameters:
@@JobOwner NVARCHAR(128) - Set the new owner name here';
IF @Help = 0



SELECT [Name] AS [JobName],
COALESCE(SUSER_SNAME([Jobs].[owner_sid]),'') AS [CurrentOwner],
'EXEC msdb.dbo.sp_update_job @job_name=N'''+[Name]+''', @owner_login_name=N'''+@JobOwner+''';' AS [ChangeToNewOwner],
'EXEC msdb.dbo.sp_update_job @job_name=N'''+[Name]+''', @owner_login_name=N'''+COALESCE(SUSER_SNAME([Jobs].[owner_sid]),'')+''';' AS [RevertToOriginalOwner]
FROM   [MSDB].[dbo].[sysjobs] [Jobs]
WHERE  [Jobs].[owner_sid] != @UserSid;

RAISERROR('No SID found for the owner name you have provided - please check the owner name and try again',11,1);



Thanks for Reading 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: