The Owner or Creator of a DTS package has been the cause of much concern
yet most people can safely ignore it altogether.
The biggest concern raised is about deleting accounts that own packages.
If a package is owned by Fred and you delete the SQL Server login or Windows account Fred,
it will not cause any problems at all.
There is one issue that can cause
problems for non-system administrators.
When saving a local package Enterprise Manager calls
sp_add_dtspackage found in the msdb database.
If you are not the owner and not a member of the sysadmin role an
error will be returned. If you fail this check the error "Only the owner
of DTS Package 'MyPackage' or a member of the sysadmin role may create
new versions of it." will be returned. A similar check
is contained within sp_drop_dtspackage which is used when
If multiple non-sysadmin developers all need to work on the same
package this limitation can cause major problems. One totally
unsupported workaround would be to remove the checks from
the stored procedure. I have implemented this successfully in
development environments where teams all need to
work with the same packages, without any problems.
Another less drastic, but more limited solution is
to use the undocumented stored
sp_reassign_dtspackageowner [@name =] 'name',
[@id =] 'id',
[@newloginname =] 'newloginname'
Both the @name and @id parameters are required as a
package name is not guaranteed to be unique.
spDTSChangeOwner, available on the
downloads page, is a convenient
wrapper around sp_reassign_dtspackageowner. It accepts two
parameters @old_owner and @new_owner, which should be self
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