Change the Maintenance Plan Owner

  • Shashank Srivastava

    Mr or Mrs. 500

    Points: 584

    Comments posted to this topic are about the item Change the Maintenance Plan Owner

    Regards
    Shashank Srivastava
    MCITP - SQL SERVER 2008
    INDIA
    Follow me @ http://shashanksrivastavasqldba.blogspot.com/

  • Dana Medley

    SSCertifiable

    Points: 6764

    Good read. This has actually bit me in the butt before with almost the exact scenario you posted in the article.



    Everything is awesome!

  • Shashank Srivastava

    Mr or Mrs. 500

    Points: 584

    Thanks.

    Regards
    Shashank Srivastava
    MCITP - SQL SERVER 2008
    INDIA
    Follow me @ http://shashanksrivastavasqldba.blogspot.com/

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    For 2005, 2008, or 2008 R2, you should check column packagetype=6 to make sure you are only updating maintenance plan packages.

    The script below works with 2005, 2008, or 2008 R2 maintenance plans.

    Change Maintenance Plan Owner

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164441

  • rollercoaster43

    Ten Centuries

    Points: 1305

    Shashank, Good Post!! I have a query on this.. Once we change the owner of Maintenance Plan,

    Is there a script we can use to update the password for that account?

    EveryTime I Change the PWd of the owner login I keep updating the password manually by going to Manage Connection tab inside the package.

    Any Idea if we can script this activity as well to get this updated in the Maintenance plan as well??

  • Shashank Srivastava

    Mr or Mrs. 500

    Points: 584

    Thanks for the nice suggestion ,its really good. But here i was doing it by the maintenance plan name so didn't want to use package type.

    Regards
    Shashank Srivastava
    MCITP - SQL SERVER 2008
    INDIA
    Follow me @ http://shashanksrivastavasqldba.blogspot.com/

  • Shashank Srivastava

    Mr or Mrs. 500

    Points: 584

    I don't think you need to update the owner password in maintenance plan after changing it in SQL Server. It will automatically take it from the SQL Server. If you have faced such issue then let me know and I will investigate it further.

    Regards
    Shashank Srivastava
    MCITP - SQL SERVER 2008
    INDIA
    Follow me @ http://shashanksrivastavasqldba.blogspot.com/

  • rollercoaster43

    Ten Centuries

    Points: 1305

    Dear Shashank,

    If your Maintenance Plan runs with sa password and if you do a Password reset for sa account, you need to manually open up the Maintenance Plan Package and go to Manage Connection and update the Password over there as well.

    In case the Password is not Updated, your jobs will start failing.. I am manually performing this activity to avoid job failures..

  • Shashank Srivastava

    Mr or Mrs. 500

    Points: 584

    Hi,

    Let me test it out in my dev environment and I will come back to you. In case this is happening then I will share the script to change it for all maintenance plan.

    Regards
    Shashank Srivastava
    MCITP - SQL SERVER 2008
    INDIA
    Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply