Changes in maintenance plan overwriting SQL job ownership

Sometimes some things strange happens. Usually when you are trying to fix other things. This issue happened to me when revising some SQL Agent jobs to make sure all backups were running fine. I removed to old DBA account from the “Job Owner” property, ran the job manually to see if it worked, it did, I went home happy. But before the weekend I updated a small part of the maintenance plan to make things more organized.

Then on monday I checked the Job Monitor only to find out that they all failed. And the old DBA account was there, mysteriously making all my jobs fail because of bad authentication. This is not something nice to find out, but at least I have found the reason – and… tada! – it’s a bug!

When you change anything on a maintenance plan created by another person, the original creator will be stored as the owner of the job every time you make a change in the SSIS package. This happens because SQL will try to keep the owner of both objects in sync. But the owner of the package is not exposed, so you have to update MSDB by hand to change it to any other thing – lets say “sa” for example. In the following article you will find more information about it:

http://dba.stackexchange.com/questions/13797/why-is-my-maintenance-plan-save-overwriting-job-ownership

This is a bug not yet fixed in SQL 2008 R2, as this Microsoft Connect article points (it says “fixed”, but it is not, you just have a workaround). Inside this article you have the workaround posted by other people who faced the same issue. I will post the code I used here to make your life (and mine) easier:

--view all packages
select * from msdb.dbo.sysssispackages

--view plans ownership
select [ownersid] , SUSER_SNAME([ownersid])
from msdb.dbo.sysssispackages
where [name] like 'MaintenancePlan%'

--update plans ownership
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] like 'MaintenancePlan%'

If you want to see a little more code, check this article at SQLDBPool. From there you have a query that will list all your jobs and owners:

-- view all jobs and owners
-- http://sqldbpool.com/2012/02/28/sql-server-agent-job-owner/
SELECT j.name, l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

To prevent this issue, I recomend using SA as owner of any jobs (and maintenance packages). I believe this is not a security issue because the job owner does NOT run the job. This account is only checked when the job starts, any subsequent action inside the job will be authenticated by the account used to run the SQL Agent service in case of windows auth, or by the specific SQL account in case of SQL authentication mode (like inside a SSIS package).

cya!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , , , , . Bookmark the permalink.

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