Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I change the owner of a DTS package?

Status
Not open for further replies.
pdalberth

pdalberth

IS-IT--Management
Nov 22, 2000
13
US
I have some DTS packages that were imported from another server. I need to change the owner - sp_changeobjectowner does not work for DTS packages.

Alternatively, I could grant the privilege to modify the packages in question to other users. I can't figure out how to do this, either. BOL suggests granting access to the msdb database - this did not work.

Any suggestions? Thanks in advance.
 
Sort by date Sort by votes
I'm not sure ownership is the problem. From BOL:

DTS packages provide two levels of security: owner and user. When a package is saved it can be assigned an owner password, a user password, or both. Users wanting to edit the package must specify the owner password, users wanting to execute the package must specify either the owner or user password.

Granting access to MSDB probably only applies to local packages saved to SQL Server; these packages are stored in the MSDB.sysdtspackages table, not as discrete files, which sounds like what you have. Or is it? When you say imported, do you mean that the files were copied, or that they were imported from one server's MSDB to the other?

Robert Bradley

 
Upvote 0 Downvote
Hi Robert,

Thanks for your response. The files were imported from one server's msdb database to the current server's msdb database. The packages are stored in native DTS format in the msdb database.

- Paul
 
Upvote 0 Downvote
It turns out that what I found what I need in the undocumented stored procedure sp_reassign_dtspackageowner.

Thanks for your help.

- pd
 
Upvote 0 Downvote
Something I encountered today that is related to this topic: DTS packages I created, where the list of Local Packages showed me as the owner, could not be opened by a colleague with gobs of permissions.

The solution: the colleague did not have Service Pack 2 installed. After SP2 installation, no problem.

Robert Bradley

 
Upvote 0 Downvote
Yes, I had the same problem. Use SP2 before you run this proc...

Tom
 
Upvote 0 Downvote
What is the complete syntax for sp_reassign_dtspackageowner??
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 
Upvote 0 Downvote
I don't understand what you mean. Are the stored procedures listed and described in an msdb table?
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 
Upvote 0 Downvote
This proc exists in the msdb database...

The code for this proc can be viewed by selecting the msdb database in enterprise manager, then the stored procedures folder, then by double-clicking on the sp_reassign_dtspackageowner proc.

Tom Davis
tdavis@sark.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
358
f0rg3tfu1
f0rg3tfu1
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
521
BobSuruncle
BobSuruncle
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
304
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
566
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
206
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top