Script to update owner for all SQL Agent Jobs to SA

Problem:

I have many SQL Agent Jobs in my SQL Server and they are running under different accounts, I want to change the owner for all this jobs to SA in one go. I don’t want to manually change owner through ssms for each job.  Is there a script for this.

 

Solution:

Below is the SQL Script to update owner for all SQL Agent Jobs to SA.

First: Script to find out SQL Agent Job details (Name, Status and Owner)

https://exploresqlserver.wordpress.com/2016/11/15/script-to-check-list-of-sql-jobs-their-status-and-owner/

 

USE msdb

go

SELECT j.[name] AS ‘JobName’,
Enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM   msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER  BY j.[name]

go


 

jobdetails1

 

Below is the script to generate job owner update commands for all SQL Agent Jobs to sa

Script:

SET nocount ON

SELECT ‘EXEC MSDB.dbo.sp_update_job ‘ + Char(13)
+ ‘@job_name = ‘ + Char(39) + j.[name] + Char(39)
+ ‘,’ + Char(13) + ‘@owner_login_name = ‘
+ Char(39) + ‘sa’ + Char(39) + Char(13) + Char(13)
FROM   msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE  l.[name] <> ‘sa’
ORDER  BY j.[name]

 

jobdetails2

Copy the generated script ,paste in New Query window and execute. This will change all SQL Jobs owner to SA.

 

jobdetails3

 

You can check Job owner details :

USE msdb

go

SELECT j.[name] AS ‘JobName’,
Enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM   msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER  BY j.[name]

go

jobdetails4

 

 

Advertisements
Leave a comment

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

%d bloggers like this: