UNDERCOVER TOOLBOX: Sp_ChangeJobOwnerShip – Changing Ownership in bulk.

road-sign-63983_1280

 

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]
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 🙂

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 )

Google+ photo

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

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: