How to change the owner of a DTS Package
-
I moved several DTS Package from one server to another using the "open/save as" method. I noticed the owner of the DTS Packages had changed to the login in which I was logged into SQL EM. I want to change the owner back to the original owner. Is there anyway to change the owner of a DTS Package? (I am not sure if sp_changeobjectowner would work.) Is there another method which I should had used to move the DTS Packages?
Thanks in advance, Kevin
-
There is an undocumented stored proc called sp_reassign_dtspackageowner [@name =], [@id=], {@newloginname]
Both @name and @id are required.
Be aware that it works with owner_sid in sysdtspackages. The owner is a property of the package object. This is a read-only property.
Every time you save a local package, a new row is written to sysdtspackages. This means that changing the value of the owner column is not a permanent solution, since the next time you save the package, the new row uses the package Creator property, which did not change. The best way to permanently show a change of owner is to SAVE AS. This creates a new package, not just a new version of the current package.
Bon chance avec la.
Quand on parle du loup, on en voit la queue
-
You can find it also explained here http://www.sqldts.com/default.aspx?212 along with a wrapper procedure to change multiple packages at a time.
Patrick,
long time no see. When I look at your postcount, it seems you haven't been quite active. I've been away from here for month and now I find you still well below 500. Is your family keeping you busy or did you finally manage to get to France and are still recovering from this green liquor
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Laughing. Salut.
I'm still in the US. Like many here I have been redundant for months, so more energy was spent finding the next place than chatting with my SQL buds. Finally secured a six month job as a technical writer, but within days of my start they gave me their database administration duties - so I'm doing both (Grin).
Family is good. I have not managed to journey to France yet - although that is still on my list of "To do" things.
Ciao
Quand on parle du loup, on en voit la queue
-
Hey, sounds really good.
We should think of starting some "classic" off-topic threads again.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Doing it the oldfashioned SCRIPTING way...............
First transfer the DTS packages to the new location ( you can use DTSbackup2000 for doing this...its freeware and highly recomandable ) and run the next script on this new location ( from mine own collection).
Hopes this will help you out....
GKramer
The Netherlands
********************************************
-- Change owner of all DTSpackages to SA
DECLARE @v_PackageName VARCHAR(128)
DECLARE @v_PackageID VARCHAR(128)
DECLARE c_Packages CURSOR FOR
select distinct(name), cast(id as varchar(128)) from msdb.dbo.sysdtspackages
OPEN c_Packages
FETCH NEXT FROM c_Packages INTO @v_PackageName, @v_PackageID
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Changed owner of DTSpackage ' +@v_PackageName+ ' (ID=' +@v_PackageID+ ') to SA'
exec ('exec msdb.dbo.sp_reassign_dtspackageowner @name = [' +@v_PackageName+ '], @id = [' +@v_PackageID+ '], @newloginname = [sa]')
--Fetch the next object from the CURSOR
FETCH NEXT FROM c_Packages INTO @v_PackageName, @v_PackageID
END
CLOSE c_Packages
DEALLOCATE c_Packages
********************************************
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply