Securing packages in DTS involves controlling access to them ”wherever they are stored ”and ensuring that they are regularly backed up to protect against disaster. Specifically, you must limit the ability of unauthorized users to edit, execute, create, or delete packages in any storage location. For packages stored in SQL Server or in Meta Data Services, a user must have a login account to even attempt to access a package. This login account is your first line of defense for packages stored in the msdb database. You can use passwords to protect packages stored in SQL Server or in a structured storage file. Packages stored in the file system should also be protected using NTFS permissions.
Packages saved to SQL Server are stored in the sysdtspackages table in the msdb database, and only members of the sysadmin server role can modify or delete existing packages. The sp_add_dtspackage system stored procedure in the msdb database is used to create new packages, and the sp_get_dtspackage system stored procedure is used to view existing packages. By default, the public role has EXECUTE permission on each of these stored procedures, which means that any user with a valid login can create a new package or view an existing package (including all its details). You should revoke EXECUTE permission on these stored procedures from the public role to limit who has permission to save or view DTS packages in SQL Server. To prevent unauthorized users from even seeing the list of packages stored in SQL Server, you should also revoke EXECUTE permission on the sp_enum_dtspackages stored procedure from the public role in the msdb database. Grant EXECUTE permission on these three stored procedures only to those users who should be creating new packages or viewing existing packages.
A package stored in SQL Server can also be secured with an owner password to further restrict access to the package.
DTS packages saved to Meta Data Services are stored in tables whose names begin with Rtbl using stored procedures whose names begin with R_i . By default, only members of the sysadmin server role or the RepositoryUser fixed database role in the msdb database can create, view, modify, or delete packages stored in Meta Data Services.
A package stored in Meta Data Services cannot be secured with an owner password, but it can be viewed or modified only by members of the sysadmin server role or the msdb database RepositoryUser fixed database role.
Because packages stored as structured storage files or as Visual Basic files are not protected by SQL Server, you must use NTFS permissions to prevent unauthorized users from gaining access to these packages. A recommended best practice is to place all packages in a common folder structure, place the desired NTFS permissions on the top-level folder, and use inherited permissions to automatically restrict access to the subfolders .
A package stored as a structured storage file can also be secured with an owner password to further restrict access to the package. However, a package stored as a Visual Basic file cannot be secured with an owner password. Any user who can access a Visual Basic file can read or modify the file.
When you save a package to SQL Server or as a structured storage file, you can use an owner password to limit who can open, edit, and execute the package. You cannot use passwords to secure packages saved to Meta Data Services or as Visual Basic files. When a package is saved with an owner password, only users who know the owner password can open or edit a package. When a package is saved with an owner password and a user password, users who know only the user password can execute the package, but they will not have permission to open or edit the package.
Packages saved to SQL Server or Meta Data Services should be regularly backed up as part of your backup of SQL Server system databases. Since these packages are stored in the msdb database, backing up the msdb database automatically backs up these packages. Packages saved to structured storage files should be regularly backed up as part of your backup of the file system. Packages saved as Visual Basic files are not as critical to back up because they are generally used only as code examples and are relatively easy to regenerate.