SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update dts owners after restore of msdb


Update dts owners after restore of msdb

Author
Message
Moenier Davids
Moenier Davids
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 380
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 itHehe

Hope someone out there can help :-D
Thanks
MANU-J.
MANU-J.
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2962 Visits: 8766
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
Moenier Davids
Moenier Davids
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 380
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'
MANU-J.
MANU-J.
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2962 Visits: 8766
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search