Securing DTS

Securing SQL Server 2000 Data Transformation Services (DTS) packages is important, especially if you work with DTS a lot. By default, anyone with a login to SQL Server can create a DTS package and save it to SQL Server. But you don't want everybody who has a login to create new DTS packages or execute existing packages, particularly on a production system. In addition, if you don't control the creation and execution of DTS packages, the msdb database can grow too large for you to manage.

You can restrict access to DTS packages by assigning an owner password and user password. An owner password prevents users from opening (and thus from editing or changing) a package. A user password prevents users from executing a package. If you set a user password, you must also set an owner password. But you can set an owner password without a user password. You can specify that users supply these passwords when they save a DTS package in the DTS Designer or when they create a DTS package by using the DTS Import or Export wizards.

But you can require owner and user passwords only when users save DTS packages to SQL Server or as a structured storage file. Passwords aren't an option when users store DTS packages to Meta Data Services or Visual Basic File storage types. But other methods exist for securing your DTS packages.

If any users have access to the sysdtspackages table in the msdb database, you can prevent them from viewing DTS packages on the server by denying SELECT permissions on sysdtspackages. (By default, the public role doesn't have SELECT permissions on this table.) You can also prevent users from viewing DTS packages by denying EXEC permissions on the undocumented sp_enum_dtspackages stored procedure in the msdb database. This stored procedure lists the DTS packages that are available on the server. By default, the public role has EXEC permissions for this stored procedure.

By default, the public role also has EXEC permissions on the undocumented sp_add_dtspackage stored procedure, which resides in the msdb database. If users have permissions for this stored procedure, they can use it to add or create DTS packages. To prevent users from adding DTS packages, you can deny EXEC permissions on this stored procedure.

We started using these techniques when we observed a lot of DTS packages on our server that had names such as TestPackage_V1, TestPackage_V2, and TestPackage_V3. We realized that users were testing their DTS skills by creating these packages. At that point, we decided we needed security for our DTS packages. By keeping owner passwords and using the other techniques described above, we could prevent users from opening or creating DTS packages.

We suggest that you assign a couple of DBAs to create DTS packages and keep one owner password for all the packages (remembering separate passwords for all the packages would be too cumbersome). Memorize your password as you do the sa password. These techniques have effectively helped us secure our DTS packages.

—Krishna Sonti
[email protected]