April 1, 2008 at 7:12 am
Hello,
I have this Development Server, our developers create and change packages, we are trying to avoid giving them the sysadmin role.
I need an assisstance to this, can I provide them the enough permissions without giving them the sysadmin role.
Presentally they are getting this message,
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : Only the owner of DTS Package 'importRevProdFromSAP' or a member of the sysadmin role may create new version of it,
Please Advice,
Thanks,
April 1, 2008 at 9:09 am
Are these DTS packages or SSIS packages? Are they stored in SQL Server or in the file system?
Greg
April 1, 2008 at 9:12 am
They are DTS Packages, they are stored on the server itself,
Thanks,
Dev
April 1, 2008 at 11:22 am
So developers need to edit packages they don't own? I think package passwords work the same for legacy packages in SQL 2005 as they did in SQL 2000. The owner of a package can save it with an owner password. Anyone who opens the package and provides the password can save a new version of it.
I haven't actually had the need to do this in SQL 2005, but, from what I see, the above should work.
Greg
April 1, 2008 at 11:39 am
Thanks Greg,
I will try that, another request,
Is there any way to transfer all the packages at 1 shot from one server to another,
Please Advice,
Thanks,
Dev
April 1, 2008 at 11:50 pm
You can save the DTS package as a SSF file (.dts) extension.
Then copy the files from Server A to Server B and then open package
in Server B and save it to SQL Server in Server B.
April 2, 2008 at 12:35 am
In SQL2000 a user with rights on the destination server can save a DTS package directly to the new server.
Open the package in the first server
Choose Package->Save As from the menu
Enter the destination server name in the boxes
Click OK to save
This is the process we use to move new packages from Acceptance to Production
April 2, 2008 at 8:50 am
Dev,
Check out DTSBackup 2000 at http://www.sqldts.com/242.aspx. It will copy all the packages in an instance. The "direct transfer" method will preserve the package layout.
Greg
April 2, 2008 at 9:02 am
Try to give the TARGETSERVER ROLE to that user in msdb DB.
December 1, 2009 at 5:22 am
Actually managed to grant the TargetServerRole to the User, but I'm still getting the same error
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply