Changing Owner of DTS Package

[Kevin Downs]

Do you know any way to change the owner of a DTS Package?

--
Regards,
Kevin

[Rob Vieira]

This is a tough one. I'll give you the safe way, and a little information
(which will make you quite dangerous - proceed at your own risk).

The safe way appears to only work under 7.0. This is to open the package in
the designer while logged in as the new user, and then perform a "Save As".
Unfortunately, there was a bug in this (it would get changed in the SID, but
not elsewhere - you would have problems because of this) until Service Pack
2 - at which time it was fixed.

So why do I say it only works under 7.0? Well, because the fix appears to
have not been propogated to SQL Server 2000 as of the RTM. I had not
previously been aware of this being a problem under 2000, but I will report
it to MS.

So, now the dangerous information. I can't warn you enough about the dangers
of playing around with system tables, but, it is one possible option for
you - proceed at your own risk (you have been warned).

The owner information exists in the sysdtspackages table in the msdb
database (assuming you are storing it locally). You would be interested in
two columns - the owner and the owner_sid. The the owner name you will
likely know by memory, the owner_sid is a bit more tricky - you need to pick
that up from the sysxlogins table in the master database.

Again, direct updating of system tables is not something to trifle with.
It's something that can down your entire server (just ask me - I managed to
trash a server doing it about 4 or 5 year ago). If it's that important to
you, then proceed at your own risk, but my personal advice would be to
rebuild the package from scratch if you are running under SQL Server 2000.


--
Rob Vieira MCSD, MCT, MCDBA
www.ProfessionalSQL.com

"Kevin Downs" <kdowns@optushome.com.au> wrote in message
news:3a706e89$1@news.devx.com...
> Do you know any way to change the owner of a DTS Package?
>
> --
> Regards,
> Kevin
>
>