Expert SQL Server™ 2005 Integration Services

9.4. Security

The only login option you have to connect to the SSIS service is to use your Active Directory account. After you connect, you'll only see packages that you are allowed to see. This protection is accomplished based on package roles. Package roles are only available on packages stored in the msdb database. Packages stored on the file system must be protected with a password.

Package roles can be accessed by right-clicking a package that you want to protect and selecting Package Roles. The Package Roles dialog box, shown in Figure 9-12, enables you to choose the role that will be in the Writer Role and Reader Role. The Writer Role can perform administration-type functions (such as overwriting a package with a new version, deleting a package, managing security, and stopping the package from running). The Reader Role can execute and view the package. The role can also export the package from Management Studio.

Figure 9-12. The Package Roles dialog box

Package roles use database roles from the msdb database. By default, people who are in the db_dtsadmin and db_dtsoperator database roles, or the creator of the package, can be Readers. The Writer Role is held by members of the db_dtsadmin database role, or the creator of the package, by default. When you select the drop-down list box in the Package Roles dialog box, you can change the package role from the default one to ...