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.

USE msdb;
GO
DECLARE @OwnerChangeFrom VARCHAR(256) = 'MyDomain\Gomer.Clown'
, @OwnerChangeTo VARCHAR(256) = 'sa'
, @OwnerSidChangeTo VARBINARY(85);
SELECT@OwnerSidChangeTo = sp.sid
FROM sys.server_principals sp
WHERE sp.name = @OwnerChangeTo;
BEGIN TRAN;
SELECTj.name AS JobName
, sc.name AS CategoryName
, ISNULL(sp.name, SUSER_SNAME(j.owner_sid)) AS OwnerName
, j.owner_sid
, j.date_created
FROMdbo.sysjobs j
LEFT OUTER JOIN sys.server_principals sp
ON j.owner_sid = sp.sid
INNER JOIN syscategories sc
ON j.category_id = sc.category_id
WHERE j.owner_sid = SUSER_SID(@OwnerChangeFrom);
UPDATEj
SET owner_sid = @OwnerSidChangeTo
FROMdbo.sysjobs j
LEFT OUTER JOIN sys.server_principals sp
ON j.owner_sid = sp.sid
INNER JOIN syscategories sc
ON j.category_id = sc.category_id
WHERE sp.name = @OwnerChangeFrom
--OR sp.name IS NULL
OR j.owner_sid = SUSER_SID(@OwnerChangeFrom);
SELECTj.name AS JobName
, sc.name AS CategoryName
, sp.name AS OwnerName
, j.owner_sid
, j.date_created
FROMdbo.sysjobs j
INNER JOIN sys.server_principals sp
ON j.owner_sid = sp.sid
INNER JOIN syscategories sc
ON j.category_id = sc.category_id;
--ROLLBACK TRANSACTION;
--COMMIT TRANSACTION;

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.