Update Job owner for all SQL Agent jobs;

Question

Hi  Is it possible to update the job owner for all sql agent jobs? I have several jobs on a server don't want to do it manually. I am running sql 2008 EE.

Thanks

 

Answer 1

USE

[msdb]

GO

EXEC

msdb.dbo.sp_update_job @job_name=N'BlockDetector', @owner_login_name=N

'sa'

GO

This would be the code that you essentially need; however if you are wanting to peridically update a number of Jobs then you will need a robust process.

One method would be to use a cursor.  Use the cursor to fetch in each job name into a variable; and then build and then build a string based on the job name and then execute the string.

If it's a one-off job then run the following: -

USE MSDB

GO

Select name from sysjobs

Copy the results into notepad, put the string

SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'

before each listing, put the string

', @owner_login_name=N','sa'

after each listing.  Copy the entire lot back into Management Studio and then execute the script.

 

---

Please click "Mark As Answer" if my post helped. Tony C.

Answer 2

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs 

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END 

CLOSE My_Cursor

DEALLOCATE My_Cursor

**Changes the owner of the jobs to sa

Answer 3

You can do this with Powershell.  I published a blog post that includes the necessary code example to do this:

http://sqlskills.com/blogs/jonathan/post/Changing-SQL-Agent-Job-Owner-with-Powershell.aspx

 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null

$InstanceName = "." 
$NewOwnerLoginName = "sa"

$smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName 
$agent = $smosvr.JobServer;

$agent.Jobs | % { 
  $_.set_OwnerLoginName($NewOwnerLoginName); 
  $_.Alter(); 
  }

---

Jonathan Kehayias | Senior Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Thanks Jonathan and others

Answer 5

you can exclude the jobs where the owner is not the SA instead updating all the jobs.

DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT j.name FROM msdb..sysjobs j INNER JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid where l.name <> 'sa'
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
        @job_name = @name_holder,
        @owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END 
CLOSE My_Cursor
DEALLOCATE My_Cursor

---

Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

Answer 6

a small correction.
instead joining to the master.dbo.syslogins, i have used IN query as some of the sid might not match all the time.

create proc dba_reset_job_owner_to_sa
as

DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT name FROM msdb..sysjobs where owner_sid not in (select sid from Master.dbo.syslogins where name = 'sa')

--SELECT j.name FROM msdb..sysjobs j left outer JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid where l.name <> 'sa'

OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
        @job_name = @name_holder,
        @owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END 
CLOSE My_Cursor
DEALLOCATE My_Cursor

---

Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

Answer 7

It might be better to slightly modify the T-SQL as shown below for fetching the jobs that do not have 'sa' as owner.

SELECT name JobName, SUSER_SNAME(owner_sid) JobOwner FROM msdb..sysjobs where owner_sid in (SELECT owner_sid FROM msdb..sysjobs where  SUSER_SNAME(owner_sid) != 'sa')

If the job is owned by a domain account and that account is not removed from ActiveDirectory but is removed from Server logins, the query as given in the post will not retrieve those jobs since it will not be in syslogins.

Hence the slight change as above.

Answer 8

The nice thing about this script is it can be executed on multiple servers at once using CMS.