Home > Backup, Configuration, SQL Agent, SQL Server > The Job whose owner kept coming back……

The Job whose owner kept coming back……

December 30, 2007 Leave a comment Go to comments

I thought i would share this little quirk about the SQL Agent jobs for maintenance plans.

One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job <JOB_Name> has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."

So, we went around and updated the job owners to one of our appropriate generic admin accounts. A few days later some of the jobs started to fail again with the same error, since we knew we had performed the update previously it was time to investigate how the job had been set back to the old user account.

It was quickly determined that the only jobs that had reverted back to the old owner were the jobs created by maintenance plans so we focused our attention here. It turns out that when you save a change to a maintenance plan the job owners are reset to the owner of the maintenance plan. The owner of the maintenance plan will be the account used to connect to the server in SSMS when creating the plan.

With this determined a slight variation of our fix was deployed. First we changed the job owners and next we updated the owner of the maintenance plan using the script at the end of the post. The script is in two parts, the first part shows you who owns what and the second updates the owner to an account you specify.

Agent jobs being created with a user account have always been a procedural problem. This is simply another variation on the problem that we need to take into consideration and put a process in place to deal with. The most likely processes are either to only create a maintenance plan when logged on with a generic account or run the script after creating the maintenance plan.

I am however curious why Microsoft have implemented updating the jobs in this manner and see it as having the potential to cause significant problems in environments that may not be monitoring their jobs as closely as is required and end up with maintenance tasks not running for some time. How to get around this? Well, given the nature of maintenance plans and the fact you must be a sysadmin to see or create them, surely it makes sense to have the owner as the SQL Service account or a user created by SQL for maintenance plans? Currently someone has posted this feature to connect here and i’ve added my two pennies worth so if you feel it should change then have you say too!

--See who owns which packages

SELECT

name, description,SUSER_SNAME(ownersid)

FROM

msdb.dbo.sysdtspackages90

--Now we update the owner to an appropriate domain account. Either the service account or a generic admin account is good.

UPDATE

msdb.dbo.sysdtspackages90

SET

OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')

WHERE

OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: