Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update dts owners after restore of msdb Expand / Collapse
Author
Message
Posted Wednesday, July 15, 2009 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:01 AM
Points: 37, Visits: 347
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

Hope someone out there can help
Thanks
Post #753307
Posted Thursday, July 30, 2009 12:26 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 10:30 PM
Points: 1,688, Visits: 8,759
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
Post #762667
Posted Friday, July 31, 2009 1:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:01 AM
Points: 37, Visits: 347


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'
Post #762929
Posted Friday, July 31, 2009 10:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 10:30 PM
Points: 1,688, Visits: 8,759
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
Post #763519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse