Changing SQL Agent Job Owner with Powershell

This blog post came from a question on the MSDN Forums asking how to change the owner of all SQL Agent Jobs on a server without having to do it manually.  I had to accomplish a similar task in the past, where I needed to scan a list of servers for jobs that were owned by a specific AD account and then change the owner to a different account after an employee left the company I was working for.  I did a quick edit to that script and created the following script which will change the Job owner of every job on a server:

[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();
    }

This script is really simple and uses SMO to connect to an instance and then iterates the Jobs in the JobServer changing the OwnerLoginName to the specified name and commiting the changes by calling the Alter() method of the Job object in SMO.

4 thoughts on “Changing SQL Agent Job Owner with Powershell

  1. Thanks Jonathan, that’s very helpful indeed. There were servers where the biztalk install had created several jobs, and the job owner ended being the default login of the person who did the install and We had to update it to sa.

  2. Thanks Jon, can $agent.jobs be modified to $agents.$($JobName) with jobname as parameter to update job owner for specific sql agent job or do you recommend any other way?

    1. $agent.Jobs is a collection, so you would have to do $agent.Jobs[$JobName] to get a specific job like this:

      $JobName = "syspolicy_purge_history"
      $Job = $agent.Jobs[$JobName]
      $Job.set_OwnerLoginName($NewOwnerLoginName);
      $Job.Alter();

      No need to pipe it because it’s a singleton.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Bitten by SSD Bit Rot

Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.