DTS Saved with Wrong Owner?

[Ovatvvon]

I am the DBA for one of our SQL 2000 Servers. I created a DB Role in MSDB to allow execute permissions on all SP's related to DTS creation/modification/dropping, etc, then granted access for a couple SQL ID's and a Network ID to that Role.

When the user opens and resaves a current DTS package, no matter which ID he uses to save it (his network ID, or the SQL ID's), the package is saved with having my network ID as the owner of the package. No matter if I reset it back to his, or the SQL ID's, this continues to happen.

Does anyone know what may be causing this? I thought whoever saves the package is set as the owner. (?)


-Ovatvvon :-Q

 

[Ovatvvon]

Nevermind, I found the answer.

Apparently, for SQL 2000, whoever originally saves a DTS package on a SQL Server will be the owner indefinately. Even if you change the owner of the package, once it is saved again, the owner automatically reverts back to its original ID.

Only way to resolve was to set owner as user; have user save as another name; delete current package; have user open the package with the new name and save with the original name; then delete the package with the new name, and keep the package that now as the original name.

Alternatively, I was able to just give the user sufficient execute permissions on specific MSDB stored procedures, and they were able to alter the packages even if my ID remained as owner. This is sufficient, so we did not bother going through the entire process above for all 67 dts packages.



-Ovatvvon :-Q