I have a problem with developers cluttering my production server with Data Transformation Services (DTS) packages. The developers need permissions on the server, but I don't want them creating packages on it. How do I prevent certain users from creating new DTS packages on my server?
Although Microsoft doesn't provide a documented way to prevent users from creating new DTS packages, you can use the following technique to keep all but the craftiest users from saving packages on your server. Enterprise Manager uses the undocumented stored procedure sp_add_dtspackage to add package information to msdb. If you remove the permission for a user to execute this procedure, the user won't be able to use Enterprise Manager to save a DTS package to msdb. Msdb is the storage location if you select SQL Server as the storage location when you save the DTS package by using the Package Designer GUI. So, this tip will prevent your developers from saving DTS packages to your production server from the GUI.
A developer might get around this roadblock by using the DTS object model and a COM design tool such as Visual Basic (VB) to create a package, then saving the package to Meta Data Services on SQL Server. This technique is advanced, so removing the EXECUTE permission for sp_add_dtspackage will keep most SQL Server users from creating new packages because they won't be able to save them.
Use this tip with caution, and remember to check for behavior changes on your system after you apply a hotfix or service pack. I don't know of any damage that changing the EXECUTE permission for sp_add_dtspackage can cause, but you usually shouldn't tamper with undocumented system objects.