Post reply

Update dts owners after restore of msdb

  • July 15, 2009 at 4:34 am

    #103785

    Hi guys

    I've restored our msdb database from production to one of the QA environments

    and ran this script thereafter

    update msdb..sysjobs

    set originating_server = 'QA_SQL'

    where originating_server = 'PROD_SQL'

    The problem I'm experiencing is that the owner of the dts packages belongs to an account that only resides on the PROD_SQL server.

    So in other words the dts packages are failing to execute due to login failure or access denied. In order for me to get the dts package working I would need to resave the package while being logged in with a sql service account thereby changing the dts owner to the service account. Keep in mind that these dts packages are not structured storage files

    I would like to know if there is any script out there that would change the owner of all dts packages and jobs in SQL server to avoid me of manually doing it:hehe:

    Hope someone out there can help 😀

    Thanks

  • July 30, 2009 at 12:26 pm

    #1032599

    All you need to do to change the package owner is set owner_sid and owner in msdb..sysdtspackages.

    If you get all the package IDs into a table then you can join to it and do the update in a single statement.

    MJ

  • July 31, 2009 at 1:06 am

    #1032800

    I've tried running this command below. It works temporarily ...it changes or reverts back to the old owner immediately once you update the dts package. Do you have an alternative recomendation on how to solve this permenatly

    update sysdtspackages

    set owner_sid = SUSER_SID('domainewowner'),

    owner = 'domainewowner'

    where name = 'dts_name'

  • July 31, 2009 at 10:13 pm

    #1033276

    It is important to note that the checks and workarounds described are all targeted at the owner_sid and owner columns in sysdtspackages. The owner is also held in the CreatorName property of the package object itself. This is a read-only property which cannot be changed programmatically.

    Each time you save a local package, a new row is written to sysdtspackages, using the values held in the package object. This means that changing the value of the owner column will not be a permanent solution as next time you save the package, the new row uses the package Creator property which has not changed. They only way to permanently reflect a change of owner is to use the Save As function in the DTS designer. This creates a new package, not just a new version of an existing package.

    http://www.sqldts.com/212.aspx

    MJ

Viewing 4 posts - 1 through 3 (of 3 total)

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