Friday, October 19, 2012

Changing job ownership across all servers in a CMS

Central management servers (CMSes) are awesome. Powershell is awesome. By their powers combined, Captain Planet great things can be done. So, I decided it'd be a good idea to remove myself as job owner on all of the SQL Agent jobs that I've set up at my current client. Rather than click through each server and figure that all out, I wrote a small Powershell script for it. 

To use it, you'll need to pull up the interactive shell and navigate to SQLSERVER:\SQLRegistration\Central Management Server Group\<your CMS here>. Once there, do this:

Get-ChildItem -recurse | where {$_.GetType().Name -eq "RegisteredServer"} | % {
    $r_srv = $_;
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $r_srv.ServerName
    $agent = $srv.JobServer
    foreach ($job in $agent.Jobs) {
        if ($job.OwnerLoginName -ieq '<login to be replaced>') {
            $job.OwnerLoginName = 'sa'
            $job.alter()
        }
    }
}
That should keep things from breaking when I roll off. Have fun with that and remember, run responsibly.

No comments:

Post a Comment