Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    35

    Unanswered: Legacy DTS Package on SQL 2005 - Give user rights to save DTS packages...

    We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    On a development server, the developer ought to be an Administrator. On a production server, the developer better not be opening or changing packages. QA is a gray area, but I don't think developers ought to be allowed to change things there either.

    -PatP

  3. #3
    Join Date
    Oct 2007
    Posts
    35
    But is it possible to give the rights to do so without giving admin rights?
    If so how?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by laoyang
    We have a SQL server with many legacy DTS packages.
    Well there's your problem right there
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2007
    Posts
    35

    Wink

    I know that....
    lol

    But here's the error they get when they try to save a DTS package.

    Error Description : Only the owner of the DTS Package 'PACKAGE_NAME' or a mamber of the sysadmin role may create new versions of it.

    I'm not going to give admin or sa rights.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, I would not allow a developer any access at all to a production box that I didn't give to every user in the company. I would make the developers admins on development machines, and I would make them just average users on production machines. I'd have a harder time making the call on Quality Assurance machines, but my gut reaction would be to give developers db_datareader even though strict security would give them no access whatsoever (not even the ability to log on to a QA machine).

    This means that a developer can do whatever they need to do on a development machine. Once the code is finalized and put through code review, a DBA migrates the code to the test environment. After the code has passed both code review and QA, the dba then migrates the code to production. This follows well established IT best practices, and will survive every audit procedure that I have ever heard about or seen.

    Giving a developer the ability to edit production code directly will fail SOX, HIPPA, GAAP, and just about every other audit test. Do not go there, even on a "one shot" basis. The cost to your business is simply too high for me to even suggest that you might want to consider doing this.

    -PatP

  7. #7
    Join Date
    Jun 2012
    Posts
    1

    Still A Problem...How About A Solution

    This thread is old; but, it comes up in searches with regards to providing the correct security to save legacy (SQL Server 2000) DTS packages that were created by other users WITHOUT providing admin access. At this point, I have devoted at least an hour or so over the past few days trying to find a workable solution short of SAVE AS (and, NO, we are not able to or going to convert just so we can change a user and password to a connection in a few DTS packages).

    Also, side note, but the answers on this thread just exasperate the problem and provide no answers other than the opinions of IT types where all would probably be in agreement; but, where that is not what is asked (a couple of times).

    So how about it? Anyone have any easy to follow instructions on how to provide a user with permissions to save SQL Server 2000 DTS packages created by others without admin rights or saving as a different package?

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •