Quickly Change SQL Job Owners

, 2018-07-26 (first published: )

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;
	SELECT	j.name AS JobName
			, sc.name AS CategoryName
			, ISNULL(sp.name, SUSER_SNAME(j.owner_sid)) AS OwnerName
			, j.owner_sid
			, j.date_created
		FROM	dbo.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);
	UPDATE	j
		SET owner_sid = @OwnerSidChangeTo
		FROM	dbo.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);
	SELECT	j.name AS JobName
			, sc.name AS CategoryName
			, sp.name AS OwnerName
			, j.owner_sid
			, j.date_created
		FROM	dbo.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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads