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