SQL 2000: Change DTS Owner


The Owner or Creator of a DTS package has been the cause of much concern, there is a BIG issue that can cause problems for non-system administrators.
If you are not the owner and not a member of the sysadmin role the following error will be returned saving or deleting a package: "Only the owner of DTS Package 'MyPackage' or a member of the sysadmin role may create new versions of it."
The solution is to use the undocumented stored procedure sp_reassign_dtspackageowner.

  1. Open Query Analyzer
  2. Select MSDB database
  3. Get the package Name/ID with the query: SELECT DISTINCT [name], [id] FROM sysdtspackages
  4. Run the following query after setting the correct package Name/ID: exec sp_reassign_dtspackageowner @name='My Package', @id='B3DA332F-48A9-4E6B-AEAE-058EA4E2793C', @newloginname='NewOwner'
Share on Google Plus

About Vittorio Pavesi

    Blogger Comment
    Facebook Comment

6 comments:

Anonymous said...

---try this sproc that I created ----that will change the ownername of all dts packages to "sa" for package names begining with the letters "espa"

---------

CREATE PROCEDURE [dbo].[csp_Utils_Change_Owner_to_SA_for_ESPA_DTS_Packages]

AS

DECLARE @PACKAGE_NAME AS NVARCHAR(1000)
DECLARE @OBJECT_ID AS NVARCHAR(1000)

DECLARE @NEW_OBJECT_OWNER AS VARCHAR(20)

SELECT @NEW_OBJECT_OWNER= 'sa'

CREATE TABLE #TMP_DATA_DTS_PACKAGE
(
PACKAGE_NAME VARCHAR(1000),
PACKAGE_ID VARCHAR(1000)
)


INSERT INTO #TMP_DATA_DTS_PACKAGE

SELECT NAME, ID FROM msdb..sysdtspackages

WHERE NAME LIKE 'espa%'

GROUP BY NAME, ID

----------------

DECLARE proc_cur CURSOR FOR SELECT PACKAGE_NAME, PACKAGE_ID FROM #TMP_DATA_DTS_PACKAGE

OPEN proc_cur

FETCH NEXT FROM proc_cur INTO @PACKAGE_NAME, @OBJECT_ID

WHILE @@fetch_status = 0

BEGIN

exec msdb..sp_reassign_dtspackageowner @PACKAGE_NAME, @OBJECT_ID, @newloginname = @NEW_OBJECT_OWNER

PRINT @PACKAGE_NAME

FETCH NEXT FROM proc_cur INTO @PACKAGE_NAME, @OBJECT_ID

END

CLOSE proc_cur

DEALLOCATE proc_cur

DROP TABLE #TMP_DATA_DTS_PACKAGE

GO

Vittorio Pavesi said...

To copy a DTS just follow these instructions:

Open the DTS package, save it as a .dts file.
Copy this .dts file to the target machine.
Open Enterprise Manager on
that machine, right click on 'Data Transformation Services' node, and select
'Open package' option.
Select this .dts package then save it again to your target SQL Server.

Alexandre and Sandra said...

Thank you all very much for the tip!
It saved us a great amount of time!

Sergio said...

Thanks a LOT!! it works!!
from Monterrey Mexico
Sergio Fierro

Anonymous said...

Three and a half years later and it's still helping. Thanks!

Mohsin said...

dynamically create the reassign SQL...

SELECT DISTINCT [name], [id],
'exec sp_reassign_dtspackageowner @name='''+[name]+''', @id='''
+CAST([id] as varchar(36))+''', @newloginname='''+'CAPREPSERVER1\DMSSQL'''
FROM sysdtspackages