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!  

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'

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:

Here is the code:

USE [master]
GO
/**********************************************
--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
)
AS
IF @Help = 1
BEGIN
PRINT 'Parameters:
@@JobOwner NVARCHAR(128) - Set the new owner name here';
END;
IF @Help = 0
BEGIN
DECLARE @UserSid VARBINARY= SUSER_SID(@JobOwner);
IF @UserSid IS NOT NULL
BEGIN
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;
END;
ELSE
BEGIN
RAISERROR('No SID found for the owner name you have provided - please check the owner name and try again',11,1);
END;
END;

Thanks for Reading