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:
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).