Редагувати

Поділитися через

Integration Services Roles (SSIS Service)

  • Стаття

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

SQL Server Integration Services provides certain fixed database-level roles to help secure access to packages that are stored in SQL Server. The available roles are different depending on whether you're saving packages in the SSIS Catalog database (SSISDB) or in the msdb database.

Roles in the SSIS Catalog database (SSISDB)

The SSIS Catalog database (SSISDB) provides the following fixed database-level roles to help secure access to packages and information about packages.

  • ssis_admin. This role provides full administrative access to the SSIS Catalog database.

  • ssis_logreader This role provides permissions to access all the views related SSISDB operational logs.

    The list of views includes: [catalog].[projects], [catalog].[packages], [catalog].[operations], [catalog].[extended_operation_info], [catalog].[operation_messages], [catalog].[event_messages], [catalog].[execution_data_statistics], [catalog].[execution_component_phases], [catalog].[execution_data_taps], [catalog].[event_message_context], [catalog].[executions], [catalog].[executables], [catalog].[executable_statistics], [catalog].[validations], [catalog].[execution_parameter_values], and [catalog].[execution_property_override_values].

By design, there are permissions granted on views and stored procedures that are assigned to the SQL Server fixed public role. The permissions don't give users access to execute or edit packages, only permissions to interact with the internal mechanism of the SSISDB which in turn determine actual permissions.

Roles in the msdb database

SQL Server Integration Services includes the three fixed database-level roles, db_ssisadmin, db_ssisltduser, and db_ssisoperator, for controlling access to packages that are saved to the msdb database. You assign roles to a package using SQL Server Management Studio. The role assignments are saved to the msdb database.

Read and Write Actions

The following table describes the read and write actions of Windows and fixed database-level roles in Integration Services.

Role Read action Write action
db_ssisadmin

or

sysadmin		 Enumerate own packages.

Enumerate all packages.

View own packages.

View all packages.

Execute own packages.

Execute all packages.

Export own packages.

Export all packages.

Execute all packages in SQL Server Agent.		 Import packages.

Delete own packages.

Delete all packages.

Change own package roles.

Change all package roles.



**** Warning ****Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.
db_ssisltduser Enumerate own packages.

Enumerate all packages.

View own packages.

Execute own packages.

Export own packages.		 Import packages.

Delete own packages.

Change own package roles.
db_ssisoperator Enumerate all packages.

View all packages.

Execute all packages.

Export all packages.

Execute all packages in SQL Server Agent.		 None
Windows administrators View execution details of all running packages. Stop all currently running packages.

Sysssispackages Table

The sysssispackages table in msdb contains the packages that are saved to SQL Server. For more information, see sysssispackages (Transact-SQL).

The sysssispackages table includes columns that contain information about the roles that are assigned to packages.

  • The readerrole column specifies the role that has read access to the package.

  • The writerrole column specifies the role that has write access to the package.

  • The ownersid column contains the unique security identifier of the user who created the package. This column defines the owner of the package.

Permissions

By default, the permissions of the db_ssisadmin and db_ssisoperator fixed database-level roles and the unique security identifier of the user who created the package apply to the reader role for packages, and the permissions of the db_ssisadmin role and the unique security identifier of the user who created the package apply to the writer role. A user must be a member of the db_ssisadmin, db_ssisltduser, or db_ssisoperator role to have read access to the package. A user must be a member of the db_ssisadmin role to have write access.

Access to Packages

The fixed database-level roles work in conjunction with user-defined roles. The user-defined roles are the roles that you create in SQL Server Management Studio and then use to assign permissions to packages. To access a package, a user must be a member of the user-defined role and the pertinent Integration Services fixed database-level role. For example, if users are members of the AuditUsers user-defined role that is assigned to a package, they must also be members of db_ssisadmin, db_ssisltduser, or db_ssisoperator role to have read access to the package.

If you do not assign user-defined roles to packages, access to packages is determined by the fixed database-level roles.

If you want to use user-defined roles, you must add them to the msdb database before you can assign them to packages. You can create new database roles in SQL Server Management Studio.

The Integration Services database-level roles grant rights on the Integration Services system tables in the msdb database.

SQL Server (the MSSQLSERVER service) must be started before you can connect to the Database Engine and access the msdb database.

To assign roles to packages, you need to complete the following tasks.

  • Open Object Explorer and Connect to Integration Services

    Before you can assign roles to packages by using SQL Server Management Studio, you must open Object Explorer in SQL Server Management Studio and connect to Integration Services.

    The Integration Services service must be started before you can connect to Integration Services.

  • Assign Reader and Writer Roles to Packages

    You can assign a reader and a writer role to each package.

Assign a Reader and Writer Role to a Package

You can assign a reader and a writer role to each package.

Assign a reader and writer role to a package

  1. In Object Explorer, locate the Integration Services connection.

  2. Expand the Stored Packages folder, and then expand the subfolder that contains the package to which you want to assign roles.

  3. Right-click the package to which you want to assign roles.

  4. In the Packages Roles dialog box, select a reader role in the Reader Role list and a writer role in the Writer Role list.

  5. Click OK.

Create a User-Defined Role

To create a user-defined role

  1. Open SQL Server Management Studio.

  2. Click Object Explorer on the View menu.

  3. On the Object Explorer toolbar, click Connect, and then click Database Engine.

  4. In the Connect to Server dialog box, provide a server name and select an authentication mode. You can use a period (.), (local), or localhost to indicate the local server.

  5. Click Connect.

  6. Expand Databases, System Databases, msdb, Security, and Roles.

  7. In the Roles node, right-click Database Roles, and click New Database Role.

  8. On the General page, provide a name and optionally, specify an owner and owned schemas and add role members.

  9. Optionally, click Permissions and configure object permissions.

  10. Optionally, click Extended Properties and configure any extended properties.

  11. Click OK.

Package Roles Dialog Box UI Reference

Use the Package Roles dialog box, available in SQL Server Management Studio, to specify the database-level roles that have read access to the package and the database-level roles that have write access to the package. Database-level roles apply only to packages that are stored in the SQL Server msdb database.

The roles listed in the dialog box are the current database roles of the msdb system database. If no roles are selected, the default Integration Services roles apply. By default, the reader role includes db_ssisadmin, db_ssisoperator, and the user who created the package. A user who is a member of one of these roles or created the packages can enumerate, view, export, and run packages. By default, the writer role includes db_ssisadmin and the user who created the package. A user who is a member of this role and the user who created the packages can import, delete, and change packages.

The ownersid column in the sysssispackages table lists the unique security identifier of the user who created the package.

Options

Package Name
Specify the name of the package.

Reader Role
Select a role in the list.

Writer Role
Select a role in the list