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'
Tags:

About author

Vittorio Pavesi

6comments

  1. Anonymous
    11:10 AM

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

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

  3. Alexandre and Sandra
    2:10 PM

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

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

  5. Anonymous
    7:20 PM

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

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

Post a Comment