PROBLEM: I need to change the owner of several SQL Server Agent Jobs. Is there an easy way to do it?

SOLUTION: Use PowerShell and SQLPSX.

I use the Get-AgentJob function from SQL Server PowerShell Extension (SQLPSX) to find the jobs I need to modify. I need to change ownership of all SQL Server Agent jobs from the login “THERIVERsyoung” to “sa” on the server “TUIRA”. This first script will display all jobs where the owner matches the login “THERIVERsyoung”.

CLEAR 

$oSQLAgentJobs = Get-AgentJob -jobserver "TUIRA"

foreach ($job in $oSQLAgentJobs) {
    if ($job.OwnerLoginName -eq "THERIVERsyoung") {
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
    }
}

I will proceed to add the code to change the ownership for all the jobs that meet the criteria. I want to recognize Sean McCown (MidnightDBA) for providing a clear explanation on how to change the sql server job owner. I strongly recommend you watch the entire presentation on this link for a good understanding of the Alter and Refresh methods of the JobServer object. On line number 8 I change the job ownership to “sa”. On number 9 I call the Alter method to update the property I changed on line 8 and on line 10 the Refresh method is called to complete the change.

CLEAR 

$oSQLAgentJobs = Get-AgentJob -jobserver "TUIRA"

foreach ($job in $oSQLAgentJobs) {
    if ($job.OwnerLoginName -eq "THERIVERsyoung") {
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
        $job.set_OwnerLoginName("sa")
        $job.Alter()
        $job.Refresh()
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
    }
}

I decided to add an extra few lines of code to search my entire SQL Server environment for jobs owned by “THERIVERsyoung”. I leveraged the use of Central Management Server to retrieve the name of all SQL Servers by running the query on lines 4 & 5. Notice that on line 8 I use the Invoke-Sqlcmd SQL comandlet to execute the query stored in the variable $sSQL and that I run it against DBACONSOLE which is my Central Management Server.

CLEAR

$sSQL = @"
SELECT [server_name]
  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
"@

$aSQLServers = Invoke-Sqlcmd -ServerInstance "DBACONSOLE" `
               -Database "msdb" `
               -Query $sSQL

foreach ($sSQLServer in $aSQLServers) {
    $oSQLAgentJobs = $null

    $sSQLServerName = $sSQLServer.server_name
    $oSQLAgentJobs = Get-AgentJob -jobserver "$sSQLServerName"    

    foreach ($job in $oSQLAgentJobs) {
        if ($job.OwnerLoginName -eq "THERIVERsyoung") {
            $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
            $job.set_OwnerLoginName("sa")
            $job.Alter()
            $job.Refresh()
            $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
        }
    }
}

You could easily turn this solution into a function with parameters for the current login and the new login. The preceding examples show the approach I took when my boss asked me to look for and change all SQL Server Agent jobs I was set as the owner.