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.
- Open Query Analyzer
- Select MSDB database
- Get the package Name/ID with the query: SELECT DISTINCT [name], [id] FROM sysdtspackages
- 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'
6 commenti:
---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
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.
Thank you all very much for the tip!
It saved us a great amount of time!
Thanks a LOT!! it works!!
from Monterrey Mexico
Sergio Fierro
Three and a half years later and it's still helping. Thanks!
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