Any user who has a valid login for Microsoft SQL Server can create a
package and then save the package to your computer that is running SQL Server. However, in an Enterprise
environment that has many users, if everyone who has a valid login for SQL Server creates and
saves Data Transformation Services (DTS) packages to the local computer that is running SQL Server, the SQL Server performance will be decreased.
If many packages are saved to the repository, performance will be decreased on the computer that is running SQL Server.
When you save a package to Meta
Data Services, the DTS package protection options are not available. However,
one advantage of saving packages to the repository is that it enables users to
use the built-in versioning feature. Packages are saved together with a version ID that is displayed as the different dates and times that the package is opened and saved.
If package security is important, consider saving the package to SQL Server as
a local server package or as a structured storage file instead. For more
information about the various methods of saving DTS packages, refer to SQL
Server Books Online.
You can use any of the following options to
prevent users from saving DTS packages or to prevent them from viewing other
- If you save DTS packages by using an owner password and a user
password, you can limit users to only executing the package by giving the users only the user password.
- You can use the owner password both to change the package and to execute the package. You can then use the user password
only to execute the package. If you use the user password to execute the package,
users cannot change the package.
- Enable auditing through the SQL Profiler to see who is
creating packages. Then, deny those users from creating more
- If a DTS package is stored on an NTFS partition as a
structured storage file, users can apply all file permissions that come with
the NTFS file system.
- On SQL Server 7.0, users can be denied SELECT, INSERT,
UPDATE or DELETE permissions on the msdb..Tfmpackage system table. This method prevents the user from viewing, creating,
updating, or deleting packages that are stored in the SQL Server 7.0 version of
- If users are denied execute permissions to the msdb..sp_enum_dtspackages stored procedure, the users cannot view any local packages. When
the user clicks Local Packages in the SQL Server Enterprise Manager, the user receives an empty
- If you want to prevent users from creating DTS packages,
deny execute permissions to the msdb..sp_add_dtspackage stored procedure.
Any user who has default security can save and view packages in
SQL Server, unless the user is trying to save a package that has an owner
Article ID: 282463 - Last Review: August 16, 2006 - Revision: 5.2
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition