web 2.0

SQL Server - How to Force the Job Owner for a Maintenance Plan

When you create maintenance plans in SQL 2005 and above it is recommended that you login as 'sa' and create the plans. Otherwise, all the jobs created as a result of the maintenance plan will have the user id that created the plan as the job owner. This is normally not a problem unless the user id that owns the job loses privileges to the database which will result in job failure. Since the 'sa' account is usually not disabled and does not rely on AD authentication, I prefer it to be the owner for all maintenance related tasks. If you created a maintenance plan under a different user ID and you want to change it to "sa" then here is the procedure.

SQL 2005 update msdb.dbo.sysdtspackages90 set ownersid = SUSER_SID( 'sa' ) where name = 'MAINT'

SQL 2008 update msdb.dbo.sysssispackages set ownersid = SUSER_SID( 'sa' ) where name = 'MAINT'

Note: These scripts change the owner for a job named "MAINT". You will have to change the where clause to match the name of your plan.

After you run one of these scripts then re-open the maintenance plan and change the description of one of the tasks and re-save it. This will force all of the jobs to be recreated and the jobs owners will then be updated to use 'sa' instead.

Tags: , ,

DBA | SQL

Comments

Michael Ceranski , on 12/17/2009 2:29:22 PM Said:

Michael Ceranski

Hi Susan,
   I appreciate the kind words. If you ever have any interesting topics that you would like me to blog about then please let me know. I am always looking for new ideas and any feedback that you have would also be appreciated.
Thanks,
   Mike

SunStar , on 12/17/2009 2:29:22 PM Said:

SunStar

I recently came accross your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.


Susan

http://8080proxy.com" rel="nofollow">http://8080proxy.com

Comments are closed