Bandwidth Monitor
Package Ownership Issues
By Darren Green
Version 7.0/2000
Level Intermediate

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 deleting a package.

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 procedure sp_reassign_dtspackageowner

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 explanatory.

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.