Integration Services 角色 (SSIS 服務)Integration Services Roles (SSIS Service)

適用於: 是SQL Server,包括在 Linux 上 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server Integration ServicesIntegration Services 提供特定固定資料庫層級角色來協助保護對於儲存在 SQL ServerSQL Server的封裝的存取。Integration ServicesIntegration Services provides certain fixed database-level roles to help secure access to packages that are stored in SQL ServerSQL Server. 可用的角色根據您將封裝儲存在 SSIS 目錄資料庫 (SSISDB) 或 msdb 資料庫而有所不同。The available roles are different depending on whether you're saving packages in the SSIS Catalog database (SSISDB) or in the msdb database.

SSIS 目錄資料庫 (SSISDB) 中的角色Roles in the SSIS Catalog database (SSISDB)

SSIS 目錄資料庫 (SSISDB) 提供下列固定資料庫層級角色,來協助保護對於封裝和封裝相關資訊的存取。The SSIS Catalog database (SSISDB) provides the following fixed database-level roles to help secure access to packages and information about packages.

  • ssis_adminssis_admin. 此角色提供 SSIS 目錄資料庫的完整系統管理存取權。This role provides full administrative access to the SSIS Catalog database.

  • ssis_logreader :此角色提供存取所有 SSISDB 作業記錄相關檢視的權限。ssis_logreader This role provides permissions to access all the views related SSISDB operational logs.

    檢視清單包括:[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] 和 [catalog].[execution_property_override_values]。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].

msdb 資料庫中的角色Roles in the msdb database

SQL ServerSQL Server Integration ServicesIntegration Services 包含以下三個固定資料庫層級角色: db_ssisadmindb_ssisltduserdb_ssisoperator,可用於控制對儲存至 msdb 資料庫之封裝的存取。Integration ServicesIntegration 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. 您可以使用 SQL Server Management StudioSQL Server Management Studio將角色指派給封裝。You assign roles to a package using SQL Server Management StudioSQL Server Management Studio. 角色指派會儲存到 msdb 資料庫。The role assignments are saved to the msdb database.

讀取和寫入動作Read and Write Actions

下表描述 Integration ServicesIntegration Services中 Windows 及固定資料庫層級角色的讀取和寫入動作。The following table describes the read and write actions of Windows and fixed database-level roles in Integration ServicesIntegration Services.

角色Role 讀取動作Read action 寫入動作Write action
db_ssisadmindb_ssisadmin

中的多個or

sysadminsysadmin
列舉自己的封裝。Enumerate own packages.

列舉所有封裝。Enumerate all packages.

檢視自己的封裝。View own packages.

檢視所有封裝。View all packages.

執行自己的封裝。Execute own packages.

執行所有封裝。Execute all packages.

匯出自己的封裝。Export own packages.

匯出所有封裝。Export all packages.

執行 SQL ServerSQL Server Agent 中的所有封裝。Execute all packages in SQL ServerSQL Server Agent.
匯入封裝。Import packages.

刪除自己的封裝。Delete own packages.

刪除所有封裝。Delete all packages.

變更自己的封裝角色。Change own package roles.

變更所有封裝角色。Change all package roles.



** 警告 ** db_ssisadmin 角色和 dc_admin 角色的成員可以將其權限提高為系統管理員。** Warning ** Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. 之所以能夠進行此權限提高,是因為這些角色可以修改 Integration ServicesIntegration Services 封裝,而且 Integration ServicesIntegration Services 可藉由使用 SQL ServerSQL Server Agent 的 sysadmin 安全性內容由 SQL ServerSQL Server 執行。This elevation of privilege can occur because these roles can modify Integration ServicesIntegration Services packages and Integration ServicesIntegration Services packages can be executed by SQL ServerSQL Server using the sysadmin security context of SQL ServerSQL Server Agent. 若要在執行維護計畫、資料收集組和其他 Integration ServicesIntegration Services 封裝時預防此權限提高,請將執行封裝的 SQL ServerSQL Server Agent 作業設為使用有限權限的 Proxy 帳戶,或是只將系統管理員 (sysadmin) 成員加入 db_ssisadmin 和 dc_admin 角色。To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration ServicesIntegration Services packages, configure SQL ServerSQL 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_ssisltduserdb_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_ssisoperatordb_ssisoperator 列舉所有封裝。Enumerate all packages.

檢視所有封裝。View all packages.

執行所有封裝。Execute all packages.

匯出所有封裝。Export all packages.

執行 SQL ServerSQL Server Agent 中的所有封裝。Execute all packages in SQL ServerSQL Server Agent.
NoneNone
Windows administratorsWindows administrators 檢視所有正在執行之封裝的執行詳細資料。View execution details of all running packages. 停止所有目前正在執行的封裝。Stop all currently running packages.

Sysssispackages 資料表Sysssispackages Table

msdb 中的 sysssispackages 資料表包含儲存到 SQL ServerSQL Server 的封裝。The sysssispackages table in msdb contains the packages that are saved to SQL ServerSQL Server. 如需詳細資訊,請參閱 sysssispackages (Transact-SQL)For more information, see sysssispackages (Transact-SQL).

sysssispackages 資料表包括的資料行包含指派給封裝之角色的相關資訊。The sysssispackages table includes columns that contain information about the roles that are assigned to packages.

  • readerrole 資料行會指定擁有封裝之讀取權限的角色。The readerrole column specifies the role that has read access to the package.

  • writerrole 資料行會指定擁有封裝之寫入權限的角色。The writerrole column specifies the role that has write access to the package.

  • ownersid 資料行包含建立封裝之使用者的唯一安全性識別碼。The ownersid column contains the unique security identifier of the user who created the package. 此資料行會定義封裝的擁有者。This column defines the owner of the package.

權限Permissions

依預設, db_ssisadmindb_ssisoperator 固定資料庫層級角色的權限以及建立封裝之使用者的唯一安全性識別碼會套用至封裝的讀取者角色,而 db_ssisadmin 角色的權限以及建立封裝之使用者的唯一安全性識別碼則會套用至封裝的寫入者角色。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. 使用者必須是 db_ssisadmindb_ssisltduserdb_ssisoperator 角色的成員,才能擁有封裝的讀取權限。A user must be a member of the db_ssisadmin, db_ssisltduser, or db_ssisoperator role to have read access to the package. 使用者必須是 db_ssisadmin 角色的成員,才能擁有寫入權限。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. 使用者定義角色是您在 SQL Server Management StudioSQL Server Management Studio 中建立後用以指派權限給封裝的角色。The user-defined roles are the roles that you create in SQL Server Management StudioSQL Server Management Studio and then use to assign permissions to packages. 若要存取封裝,使用者必須是使用者定義角色和適當 Integration ServicesIntegration Services 固定資料庫層級角色的成員。To access a package, a user must be a member of the user-defined role and the pertinent Integration ServicesIntegration Services fixed database-level role. 例如,如果使用者是指派給封裝之 AuditUsers 使用者定義角色的成員,他們還必須是 db_ssisadmindb_ssisltduserdb_ssisoperator 角色的成員,才能擁有封裝的讀取權限。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.

如果想要使用使用者定義角色,則必須先將這些角色加入 msdb 資料庫,然後才能將它們指派給封裝。If you want to use user-defined roles, you must add them to the msdb database before you can assign them to packages. 您可以在 SQL Server Management StudioSQL Server Management Studio中建立新資料庫角色。You can create new database roles in SQL Server Management StudioSQL Server Management Studio.

Integration ServicesIntegration Services 資料庫層級角色會授與 msdb 資料庫中 Integration ServicesIntegration Services 系統資料表的權限。The Integration ServicesIntegration Services database-level roles grant rights on the Integration ServicesIntegration Services system tables in the msdb database.

SQL ServerSQL Server (MSSQLSERVER 服務) 必須啟動,才能連接到 Database Engine 並存取 msdb 資料庫。(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.

  • 開啟物件總管並連接到 Integration ServicesOpen Object Explorer and Connect to Integration Services

    必須先在 SQL Server Management StudioSQL Server Management Studio中開啟 [物件總管],並連接到 SQL Server Management StudioSQL Server Management Studio ,才能使用 Integration ServicesIntegration Services指派角色給封裝。Before you can assign roles to packages by using SQL Server Management StudioSQL Server Management Studio, you must open Object Explorer in SQL Server Management StudioSQL Server Management Studio and connect to Integration ServicesIntegration Services.

    必須先啟動 Integration ServicesIntegration Services 服務,才能連接到 Integration ServicesIntegration ServicesThe Integration ServicesIntegration Services service must be started before you can connect to Integration ServicesIntegration 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. 在 [物件總管] 中,尋找 Integration ServicesIntegration Services 連接。In Object Explorer, locate the Integration ServicesIntegration Services connection.

  2. 展開 [Stored Packages] 資料夾,然後展開包含想要為其指派角色之封裝的子資料夾。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. 開啟 SQL Server Management StudioSQL Server Management StudioOpen SQL Server Management StudioSQL Server Management Studio.

  2. [檢視] 功能表上,按一下 [物件總管]Click Object Explorer on the View menu.

  3. 在 [物件總管] 工具列上,按一下 [連接] ,再按一下 [Database Engine]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. 您可以使用句號 (.)、(local) 或 localhost 表示本機伺服器。You can use a period (.), (local), or localhost to indicate the local server.

  5. 按一下 [連接]Click Connect.

  6. 展開 [資料庫]、[系統資料庫]、[msdb]、[安全性] 和 [角色]。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.

套件角色對話方塊 UI 參考Package Roles Dialog Box UI Reference

使用 [封裝角色] 對話方塊 (可以在 SQL Server Management StudioSQL Server Management Studio 中使用),即可指定擁有封裝之讀取權限的資料庫層級角色以及擁有封裝之寫入權限的資料庫層級角色。Use the Package Roles dialog box, available in SQL Server Management StudioSQL 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. 資料庫層級角色僅適用於儲存在 SQL ServerSQL Server msdb 資料庫中的封裝。Database-level roles apply only to packages that are stored in the SQL ServerSQL Server msdb database.

對話方塊中所列出的角色是 msdb 系統資料庫的目前資料庫角色。The roles listed in the dialog box are the current database roles of the msdb system database. 如果沒有選取角色,則會套用預設 Integration ServicesIntegration Services 角色。If no roles are selected, the default Integration ServicesIntegration Services roles apply. 依預設,讀取者角色包含 db_ssisadmindb_ssisoperator和建立封裝的使用者。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. 依預設,寫入者角色包含 db_ssisadmin 以及建立封裝的使用者。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.

sysssispackages 資料表中的 ownersid 資料行列出建立封裝之使用者的唯一安全性識別碼。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