SQL Server Maintenance Plan Change Owner

International Bibliography of Periodical Liter...

Database Icon (Photo credit: Wikipedia)

If your SQL Server 2005 Maintenance Plan (backup, etc) jobs start failing for no apparent reason, check the following.

  • Is the original creator / owner of the Maintenance Plan still a valid Windows or Active Directory user?
  • Does the original creator / owner of the Maintenance Plan have full “sysadmin” level privileges on this instance of SQL Server database engine?

If you have one or both of the following problems above, use the following process to change the creator / owner.

  • Make sure you’re logged in to SQL Server Management Studio (SSMS) with a valid Windows / Active Directory account with full SQL “sysadmin” role privileges.
  • Right-click the Maintenance Plan name in SSMS  and choose “rename”.
  • Change the name to anything that makes sense to you – it must be different from the current name.
  • Once the rename has complete, the user performing the rename will be the new creator / owner of the Maintenance Plan
  • Double-check the properties of all maintenance plan “steps” to verify these owners are correct too – these can be changed in the GUI.
  • Test the maintenance plan by running one or more of the steps / jobs – they should complete successfully.

I saw this rename trick in a forum online and it was the only thing that worked for me. Feel free to comment if this helps you out too. I have not tested this on SQL 2008 or newer – if you use the same fix on SQL 2008 or 2012+, leave a comment with the results.

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database 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