Managing permissions for DTS packages in an Enterprise environment (282463)

MORE INFORMATION

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 users' packages.

• 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 packages.
• 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 the repository.
• 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 list.
• If you want to prevent users from creating DTS packages, deny execute permissions to the msdb..sp_add_dtspackage stored procedure.

Note 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 password.