Change DTS Package Owner?

Question

Post reply

Change DTS Package Owner?

Jeff Steinweber

SSC Eights!

Points: 916
More actions
May 9, 2006 at 1:51 pm

#103160

Is there a way to change the owner of a DTS package without deleting it and recreating it?
sp_changeobjectowner doesn't work for DTS packages.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Greg Charles

SSC-Forever

Points: 45403

May 9, 2006 at 1:58 pm

#636879

sp_reassign_dtspackageowner. It's in msdb.

Greg

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 2

Jeff Steinweber

SSC Eights!

Points: 916

May 9, 2006 at 2:05 pm

#636880

Thanks Greg!

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 3

This must be for SQL 2005, is there a SQL 2000 way to do it?

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 4

Never mind. It threw me off when the color of the sp_reassign_dtspackageowner did not change in Query Analyzer. Since you pointed out that it is on MSDB database, would there be any problem going right into the table (sysdtspackages) and changing the owner?

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

That would work. Make sure you update all versions.

Greg

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

But I think if you modify the DTS package at a later date, it reverts back to the original owner.

I have some notes that I ran this script to change the owner ... experiment & see if it works (and STICKS !)

update sysdtspackages

set owner_sid = SUSER_SID('DOMAIN\New_User'),

owner = 'DOMAIN\New_User'

where name = 'DTS_PackageName'

dbaforever Hall of Fame Points: 3740 More actions · Answer 7

yep hombrew01. It can sometimes revert back to the original owner. That's been very frustrating for me. I will remember your tip.

Thanks, John

Greg Charles SSC-Forever Points: 45403 More actions · Answer 8

I've never had a package revert to a previous owner after changing it with sp_reassign_dtspackageowner. That's why I use it intead of modiying the system table.

Greg

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 9

Greg,

This procedure sp_reassign_dtspackageowner is expecting three parameters @name, @ID, @newloginname

I can guess that @Name means the name of the dts package and the @newloginname is self-explainitory, but what/where is the @ID coming from and how do I find it?

dbaforever Hall of Fame Points: 3740 More actions · Answer 10

Jeff,

You can find the id in msdb's sysdtspackages table.

swede SSCommitted Points: 1773 More actions · Answer 11

try this:

update msdb..sysdtspackages

set owner_sid = (select sid from master..sysxlogins

where name = 'sql login name')

where name = 'dts package name'

Greg Charles SSC-Forever Points: 45403 More actions · Answer 12

Jeff,

Execute sp_enum_dtspackages in msdb. It will list all the package names and IDs. Just copy and past them into the parameters for sp_reassign_dtspackageowner.

Greg

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 13

How do you do it for all packages that are owned by domain\xyz, without having to do it one by one? If I use

update sysdtspackages

set owner_sid = SUSER_SID('DOMAIN\New_User'),

owner = 'DOMAIN\New_User'

where name = 'DTS_PackageName'

You are saying that it will revert back?

¤ §unshine ¤

Greg Charles SSC-Forever Points: 45403 More actions · Answer 14

You'd use the same update statement execept change the WHERE clause to

where owner = 'DOMAIN\Old_User'

As I said earlier in the thread, I've never had a package revert to a previous owner when it was modified by using sp_reassign_dtspackageowner. I'm not sure why the update statement would work any differently since it's the same statement that's in the stored procedure.

Greg