Post reply

Permission to create or change packages

  • April 1, 2008 at 7:12 am

    #183931

    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

    #796585

    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

    #796590

    They are DTS Packages, they are stored on the server itself,

    Thanks,

    Dev

  • April 1, 2008 at 11:22 am

    #796730

    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

    #796747

    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

    #797023

    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

    #797025

    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

    #797299

    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

    #797308

    Try to give the TARGETSERVER ROLE to that user in msdb DB.

  • December 1, 2009 at 5:22 am

    #1085703

    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