Quickly Change SQL Job Owners

It is not unusual to find a server where some random user created a bunch of jobs to be run by SQL Agent. Sometimes, the user creating the job(s) sets themself as the owner of the job. There are certain cases where this behavior is hard to avoid like when creating a maintenance plan.

And of course, there are times when the user just doesn’t know any better. There is of course, the rare occasion when setting the job owner to be ones self makes the most sense -but that is few and far between in the grand scheme. Usually, you will want a non-expiring account such as a service account or a principal without “logon” permissions to be the owner.

The primary reason being simple – humans have an expiration date for every job they will ever have. When that expiration occurs, you may end up with any number of unwanted side effects. Unwanted side effects is exactly what we try to avoid in our jobs run via SQL Agent.

No Expiration Date

There are two basic means to change the owner of every job on your server. Either you open each job one by one and set the owner to an acceptable principal. This method is rather tedious and you will be fighting off the boredom if you have a few hundred jobs on the server. Or, the alternative, change the job owners group by group (set-based theory). This second method can be far less tedious and far more efficient. The second method is by far my preferred method. Let’s take a look at how to make all of these changes in groups.

There are three basic sections to this script. First I fetch what should be changed, then I make the change, and lastly I verify the change. If the change doesn’t look right, then I can rollback the change. If the change is what I expected, then I can commit the change. Those are the broad strokes.

At a more detailed glimpse, I have setup a few variables to compare what I want to change, what the new job owner should be and then I fetch the sid of that new job owner. In my example, I am setting everything to ‘sa’. Why? Because it is easy for the sake of the example in the article – nothing more!

Since sometimes the owner of the job may only have access to the SQL instance via a Domain Group, I also take advantage of a couple of functions to double check that it is the correct account. These functions I am using are SUSER_SID() and SUSER_SNAME().

When all is done as I am expecting, then I should see something similar to the following.

Since the change is what I expect, then at this point I would proceed with the commit transaction statement.

The Wrap

As you can see, making job ownership changes at group scale instead of one by one is pretty easy. This only takes a matter of seconds to run against hundreds of jobs. That same kind of task done one at a time could easily take more than 40 minutes. I am not sure I want to spend that much time on such an innocuous task. I hope you are now able to use what you have learned to improve your skills and become a rock-star DBA. ENJOY!

If you feel the need to read more about single-user mode, here is an article and another on the topic.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

3 Comments - Leave a comment
  1. […] Jason Brimhall shows us a quick method for changing SQL Agent job owners: […]

  2. ScottPletcher says:

    I generate, and optionally execute, a script that uses sp_update_job to change the owner. Isn’t it rather dangerous to directly update what is in effect a system table?

    • Jason Brimhall says:

      It would be no more dangerous in this case than to use sp_update_job. I wouldn’t classify the job related tables as system tables, partly due to them being in msdb and partly due to the schema that owns the tables. Generally, the system tables that are dangerous to modify are the ones that require a dedicated administrator connection. In this case, we are talking about a job owner in a table that is modified in the same way if calling that stored procedure. One major difference, I can use a set in my example whereas the procedure requires one to loop through a set of a jobs which is rather inefficient.

      Here is the update statement from that proc.
      UPDATE msdb.dbo.sysjobs
      SET name = @new_name,
      enabled = @enabled,
      description = @description,
      start_step_id = @start_step_id,
      category_id = @category_id, — Returned from sp_verify_job
      owner_sid = @owner_sid,
      notify_level_eventlog = @notify_level_eventlog,
      notify_level_email = @notify_level_email,
      notify_level_netsend = @notify_level_netsend,
      notify_level_page = @notify_level_page,
      notify_email_operator_id = @notify_email_operator_id, — Returned from sp_verify_job
      notify_netsend_operator_id = @notify_netsend_operator_id, — Returned from sp_verify_job
      notify_page_operator_id = @notify_page_operator_id, — Returned from sp_verify_job
      delete_level = @delete_level,
      version_number = version_number + 1, — Update the job’s version
      date_modified = GETDATE() — Update the job’s last-modified information
      WHERE (job_id = @job_id)

      Notice the nonsense in updating every field when it is very unnecessary to do so. Very inefficient.

Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Calendar
July 2018
M T W T F S S
« Jun   Nov »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Welcome , today is Monday, November 19, 2018