Changing SQL Server Job Owners with PowerShell

Posted on January 26, 2011 by Sean McCown Microsoft SQL MVP in SQL Server with 0 Comments

Changing a job owner in SQL Server is relatively easy in SSMS and in T-SQL, but when you need to change it several times, it becomes a bit more involved.  While making the change in T-SQL can be done more quickly than in SSMS, it can also be tricky to code and debug.  This is where PowerShell can make your life much easier.

Here we’ll start by right-clicking on ‘Jobs’ in SSMS and choosing ‘Start PowerShell’.

Start PowerShell from SSMS

You can also get to the same place in PowerShell proper though, so no matter how you get to the ‘Jobs’ node, the rest of the operation will be the same.

Let me give you a couple of reasons why you might want to change job owners in bulk.  There are two really common scenarios that you’ll run into.  First, you want to standardize all of your jobs to a single owner (most commonly ‘sa’).  It’s a really good idea to make ‘sa’ the standard for job names because individual users come and go, but ‘sa’ will always be there.  The second scenario is when you do have jobs owned by individual users, and you need to change all the jobs owned by one user to be owned by another user.  Maybe the 1st user left the company or moved to a different job within the company.  Now that I’ve given you a couple of reasons why this could be necessary, let’s continue.

Now that PowerShell is open. Let’s start by searching for the jobs we’re interested in.  In PowerShell we can search by any number of criteria, or any combination of criteria as well.  Here we’re going to search for a specific job owner ‘kiddo\sean’.

>dir | ?{$_.OwnerLoginName –eq “kiddo\sean”}

SQL Server PowerShell: Change Job Owners

Now that we’ve verified which jobs we’ll be changing, we only have to make the change at the end to actually perform the change.

> dir | ?{$_.OwnerLoginName –eq “kiddo\sean”} | %{$_.OwnerLoginName = “sa”}

Here I’m going to show you this process in stages so the line above isn’t the final version.

SQL Server PowerShell: Change Job Owners

Above you can see that the command was typed and returned, but there’s absolutely no information on the screen about what happened.  In truth the operation returned very quickly, but if I were changing a couple hundred jobs at once it could take much longer given the amount of activity on the box.  And the one thing you don’t want when changing that many objects is to only have a blinking cursor staring at you while the operation is going on.  So to make it much better we’re going to alter the script to print the name of each job as it’s completed.

> dir | ?{$_.OwnerLoginName –eq “kiddo\sean”} | %{$_.OwnerLoginName = “sa”; $_.Name}

SQL Server PowerShell: Change Job Owners

Notice how it printed each job name as the owner was changed.  Now you can see how your operation is progressing.  Now it’s time to check whether our change occurred correctly.  To do that we’ll run a simple command to see what the job owners are:

>dir | FT Name, OwnerLoginName –auto

SQL Server PowerShell: Change Job Owners

And just to make sure, let’s check one of them in SSMS too:

SSMS: Change Job Owners

What’s this?  PowerShell and SSMS are reporting different job owners for the same job.  If you ask PowerShell, the job owner has been changed, but if you ask SSMS, it says it hasn’t.  What’s the deal with that?  And more importantly, can we get them to report the same info?  What we need is to make sure PowerShell is reporting the most up to date info.  And for that we’ll run the update method on each of the jobs to force it to go back to the server to read the real value inside of the system table.

>dir | %{$_.Refresh()}

And then we’ll just query for the job owners again.

> dir | FT Name, OwnerLoginName –auto

SQL Server PowerShell: Change Job Owners

OK, even though the command we ran to update the owner didn’t work, we’ve at least got PowerShell and SSMS reporting the same info now.  So why didn’t the original command work?  Well, it actually did; it just didn’t persist to the server.  And since we were able to refresh the PowerShell window to get the correct value, there ought to be a way to also force it to push the PowerShell session data to the server.  You’ll find that in the Alter() method.  You can see the alter method by running the following command:

>dir | gm

So now we’ll change our original command to change the owner to include the Alter() method.

> dir | ?{$_.OwnerLoginName –eq “kiddo\sean”} | %{$_.OwnerLoginName = “sa”; $_.Alter(); $_.Name}

SQL Server PowerShell: Change Job Owners

You can see above that I added the Alter() method to the first command, then refreshed the data, and then checked the owners and they’re now showing the correct updated values.  Mission accomplished.  And this is the final version of our command.  Of course, you don’t have to refresh and check the values if you don’t want to.  You can just run the command to change the owner and trust that all went according to plan, but most of us like a visual confirmation that all went well.

OK, that’s it for this time, but I’m going to leave you with one parting piece of advice.  It’s always a good idea to do a “dir | gm” and if there’s an Alter() method, use it.  It’s usually needed if it exists.

Sponsored

Tagged with