SQL Server Agent 固定資料庫角色SQL Server Agent Fixed Database Roles

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

重要

Azure SQL Database 受控執行個體目前支援多數 (但非全部) 的 SQL Server Agent 功能。On Azure SQL Database Managed Instance, most, but not all SQL Server Agent features are currently supported. 如需詳細資料,請參閱 Azure SQL Database 受控執行個體與 SQL Server 之間的 T-SQL 差異See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details.

SQL ServerSQL Server 具有下列 msdb 資料庫固定資料庫角色,讓管理員在存取 SQL ServerSQL Server 代理程式時具有更細微的控制權。has the following msdb database fixed database roles, which give administrators finer control over access to SQL ServerSQL Server Agent. 以下列出這些角色 (存取權限由少至多排列):The roles listed from least to most privileged access are:

  • SQLAgentUserRoleSQLAgentUserRole

  • SQLAgentReaderRoleSQLAgentReaderRole

  • SQLAgentOperatorRoleSQLAgentOperatorRole

當使用者不是這些角色的其中一個成員,而以 SQL Server Management StudioSQL Server Management Studio 連接到 SQL ServerSQL Server 時,他們不會在物件總管中看見 [SQL Server Agent] 節點。When users who are not members of one of these roles are connected to SQL ServerSQL Server in SQL Server Management StudioSQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. 使用者必須是這些固定資料庫角色的其中一個成員,或是系統管理員 (sysadmin) 固定伺服器角色,才能使用 SQL ServerSQL Server Agent。A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL ServerSQL Server Agent.

SQL Server Agent 固定資料庫角色的權限Permissions of SQL Server Agent Fixed Database Roles

SQL ServerSQL Server Agent 資料庫角色權限是同心而互相關聯的 -- 較多權限的角色繼承較少權限的角色在 SQL ServerSQL Server Agent 物件上的權限 (包括警示、操作員、作業、排程和 Proxy)。The SQL ServerSQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL ServerSQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). 例如,如果擁有最少權限的 SQLAgentUserRole 成員,被授與存取 proxy_A 的權限,而 SQLAgentReaderRoleSQLAgentOperatorRole 兩者的成員自動就會擁有存取此 Proxy 的權限,即使沒有明確授與他們 proxy_A 的存取權限。For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. 如此可能會有安全性的隱含意義,將會在下列章節對於每個角色討論。This may have security implications, which are discussed in the following sections about each role.

SQLAgentUserRole 權限SQLAgentUserRole Permissions

SQLAgentUserRoleSQL ServerSQL Server Agent 定資料庫角色中具備最少權限的角色。SQLAgentUserRole is the least privileged of the SQL ServerSQL Server Agent fixed database roles. 它只對操作員、本機作業和作業排程擁有權限。It has permissions on only operators, local jobs, and job schedules. SQLAgentUserRole 的成員只對他們所擁有的本機作業和作業排程擁有權限。Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. 他們無法使用多伺服器作業 (主要和目標伺服器作業),也無法變更作業擁有權來取得他們尚未擁有之作業的存取權。They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole 成員只能在 SQL Server Management StudioSQL Server Management Studio 的 [作業步驟屬性] 對話方塊中檢視可用 Proxy 的清單。SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management StudioSQL Server Management Studio. SQLAgentUserRole 的成員在 SQL Server Management StudioSQL Server Management Studio 物件總管中只能看見 [作業] 節點。Only the Jobs node in SQL Server Management StudioSQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

重要

在將 Proxy 存取權授與 SQL ServerSQL Server Agentdatabaseroles 的成員之前,需考慮安全性隱含意義。Consider the security implications before granting proxy access to members of the SQL ServerSQL Server Agentdatabaseroles. SQLAgentReaderRoleSQLAgentOperatorRole 自動為 SQLAgentUserRole的成員。The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. 這表示 SQLAgentReaderRoleSQLAgentOperatorRole 的成員可以存取所有授與 SQL ServerSQL Server SQLAgentUserRole Agent Proxy,也可以使用這些 Proxy。This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL ServerSQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies.

下表摘要出 SQLAgentUserRoleSQL ServerSQL Server Agent 物件所擁有的權限。The following table summarizes SQLAgentUserRole permissions on SQL ServerSQL Server Agent objects.

動作Action 操作員Operators 本機作業Local jobs

(僅擁有的作業)(owned jobs only)
作業排程Job schedules

(僅擁有的排程)(owned schedules only)
ProxyProxies
建立/修改/刪除Create/modify/delete No Yes

無法變更作業擁有權。Cannot change job ownership.
Yes No
檢視清單 (列舉)View list (enumerate) Yes

可以取得可用操作員的清單,以便在 sp_notify_operator 和 Management Studio 的 [作業屬性] 對話方塊中使用。Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
Yes Yes Yes

只在 Management Studio 的 [作業步驟屬性] 對話方塊中可用的 Proxy 清單。List of proxies only available in the Job Step Properties dialog box of Management Studio.
啟用/停用Enable/disable No Yes Yes 不適用Not applicable
檢視屬性View properties No Yes Yes No
執行/停止/啟動Execute/stop/start 不適用Not applicable Yes 不適用Not applicable 不適用Not applicable
檢視作業記錄View job history 不適用Not applicable Yes 不適用Not applicable 不適用Not applicable
刪除作業記錄Delete job history 不適用Not applicable No

SQLAgentUserRole 的成員必須明確被授與對 sp_purge_jobhistory 的 EXECUTE 權限,才能對他們擁有的作業刪除作業記錄。Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. 他們無法刪除任何其他作業的作業記錄。They cannot delete job history for any other jobs.
不適用Not applicable 不適用Not applicable
附加/卸離Attach/detach 不適用Not applicable 不適用Not applicable Yes 不適用Not applicable

SQLAgentReaderRole 權限SQLAgentReaderRole Permissions

SQLAgentReaderRole 包括所有 SQLAgentUserRole 的權限,並擁有檢視可用的多伺服器作業清單、其屬性與記錄的權限。SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. 此角色的成員也可以檢視所有可用作業的清單,和作業排程及其屬性,而非只有他們擁有的作業和作業排程。Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole 成員無法變更作業擁有權來取得他們尚未擁有之作業的存取權。SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. SQLAgentReaderRole 的成員在 SQL Server Management StudioSQL Server Management Studio 物件總管中只能看見 [作業] 節點。Only the Jobs node in SQL Server Management StudioSQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

重要

在將 Proxy 存取權授與 SQL ServerSQL Server Agentdatabaseroles 的成員之前,需考慮安全性隱含意義。Consider the security implications before granting proxy access to members of the SQL ServerSQL Server Agentdatabaseroles. SQLAgentReaderRole 的成員自動為 SQLAgentUserRole的成員。Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. 這表示 SQLAgentReaderRole 的成員可以存取所有授與 SQL ServerSQL Server SQLAgentUserRole Agent Proxy,也可以使用這些 Proxy。This means that members of SQLAgentReaderRole have access to all SQL ServerSQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies.

下表摘要出 SQLAgentReaderRoleSQL ServerSQL Server Agent 物件所擁有的權限。The following table summarizes SQLAgentReaderRole permissions on SQL ServerSQL Server Agent objects.

動作Action 操作員Operators 本機作業Local jobs 多伺服器作業Multiserver jobs 作業排程Job schedules ProxyProxies
建立/修改/刪除Create/modify/delete No 是 (僅擁有的作業)Yes (owned jobs only)

無法變更作業擁有權。Cannot change job ownership.
No 是 (僅擁有的排程)Yes (owned schedules only) No
檢視清單 (列舉)View list (enumerate) Yes

可以取得可用操作員的清單,以便在 sp_notify_operator 和 Management Studio 的 [作業屬性] 對話方塊中使用。Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
Yes Yes Yes Yes

只在 Management Studio 的 [作業步驟屬性] 對話方塊中可用的 Proxy 清單。List of proxies only available in the Job Step Properties dialog box of Management Studio.
啟用/停用Enable/disable No 是 (僅擁有的作業)Yes (owned jobs only) No 是 (僅擁有的排程)Yes (owned schedules only) 不適用Not applicable
檢視屬性View properties No Yes Yes Yes No
編輯屬性Edit properties No 是 (僅擁有的作業)Yes (owned jobs only) No 是 (僅擁有的排程)Yes (owned schedules only) No
執行/停止/啟動Execute/stop/start 不適用Not applicable 是 (僅擁有的作業)Yes (owned jobs only) No 不適用Not applicable 不適用Not applicable
檢視作業記錄View job history 不適用Not applicable Yes Yes 不適用Not applicable 不適用Not applicable
刪除作業記錄Delete job history 不適用Not applicable No

SQLAgentReaderRole 的成員必須明確被授與對 sp_purge_jobhistory 的 EXECUTE 權限,才能對他們擁有的作業刪除作業記錄。Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. 他們無法刪除任何其他作業的作業記錄。They cannot delete job history for any other jobs.
No 不適用Not applicable 不適用Not applicable
附加/卸離Attach/detach 不適用Not applicable 不適用Not applicable 不適用Not applicable 是 (僅擁有的排程)Yes (owned schedules only) 不適用Not applicable

SQLAgentOperatorRole 權限SQLAgentOperatorRole Permissions

SQLAgentOperatorRoleSQL ServerSQL Server Agent 固定資料庫角色中具備最多權限的角色。SQLAgentOperatorRole is the most privileged of the SQL ServerSQL Server Agent fixed database roles. 它包括 SQLAgentUserRoleSQLAgentReaderRole的所有權限。It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. 此角色的成員也可以檢視操作員和 Proxy 的屬性,並列舉出伺服器上可用的 Proxy 和警示。Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

SQLAgentOperatorRole 成員對本機作業和排程擁有額外的權限。SQLAgentOperatorRole members have additional permissions on local jobs and schedules. 他們可以執行、停止或啟動所有本機作業,也可以刪除伺服器上任何本機作業的作業記錄。They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. 還可以啟用或停用伺服器上所有本機作業和排程。They can also enable or disable all local jobs and schedules on the server. 若要啟用或停用本機作業或排程,此角色的成員必須使用預存程序 sp_update_jobsp_update_scheduleTo enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. 只有指定作業或排程名稱或識別碼的參數,以及 **@enabled** 參數可由 SQLAgentOperatorRole的成員。Only the parameters that specify the job or schedule name or identifier and the **@enabled** parameter can be specified by members of SQLAgentOperatorRole. 如果他們指定任何其他參數,執行這些預存程序會失敗。If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole 成員無法變更作業擁有權來取得他們尚未擁有之作業的存取權。SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.

SQLAgentOperatorRole 的成員可以看見 SQL Server Management StudioSQL Server Management Studio 物件總管中的 [作業] 、[警示] 、[操作員] 和 [Proxy] 節點。The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management StudioSQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. 只有 [錯誤記錄檔] 節點對此角色的成員是不可見的。Only the Error Logs node is not visible to members of this role.

重要

在將 Proxy 存取權授與 SQL ServerSQL Server Agentdatabaseroles 的成員之前,需考慮安全性隱含意義。Consider the security implications before granting proxy access to members of the SQL ServerSQL Server Agentdatabaseroles. SQLAgentOperatorRole 的成員自動為 SQLAgentUserRoleSQLAgentReaderRole的成員。Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. 這表示 SQLAgentOperatorRole 的成員可以存取所有授與 SQL ServerSQL Server SQLAgentUserRole SQLAgentReaderRole Agent Proxy,也可以使用這些 Proxy。This means that members of SQLAgentOperatorRole have access to all SQL ServerSQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies.

下表摘要出 SQLAgentOperatorRoleSQL ServerSQL Server Agent 物件所擁有的權限。The following table summarizes SQLAgentOperatorRole permissions on SQL ServerSQL Server Agent objects.

動作Action 警示Alerts 操作員Operators 本機作業Local jobs 多伺服器作業Multiserver jobs 作業排程Job schedules ProxyProxies
建立/修改/刪除Create/modify/delete No No 是 (僅擁有的作業)Yes (owned jobs only)

無法變更作業擁有權。Cannot change job ownership.
No 是 (僅擁有的排程)Yes (owned schedules only) No
檢視清單 (列舉)View list (enumerate) Yes Yes

可以取得可用操作員的清單,以便在 sp_notify_operator 和 Management Studio 的 [作業屬性] 對話方塊中使用。Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
Yes Yes Yes Yes
啟用/停用Enable/disable No No Yes

SQLAgentOperatorRole 成員可以使用預存程序 sp_update_job 並指定 **@enabled** 和 **@job_id** (或 **@job_name** ) 參數的值來啟用或停用不是由他們擁有的本機作業。SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the **@enabled** and the **@job_id** (or **@job_name**) parameters. 如果此角色的成員為此預存程序指定任何其他參數,執行程序會失敗。If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
No Yes

SQLAgentOperatorRole 成員可以使用預存程序 sp_update_schedule 並指定 **@enabled** 和 **@schedule_id** (或 **@name** ) 參數的值來啟用或停用不是由他們擁有的本機作業。SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the **@enabled** and the **@schedule_id** (or **@name**) parameters. 如果此角色的成員為此預存程序指定任何其他參數,執行程序會失敗。If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
不適用Not applicable
檢視屬性View properties Yes Yes Yes Yes Yes Yes
編輯屬性Edit properties No No 是 (僅擁有的作業)Yes (owned jobs only) No 是 (僅擁有的排程)Yes (owned schedules only) No
執行/停止/啟動Execute/stop/start 不適用Not applicable 不適用Not applicable Yes No 不適用Not applicable 不適用Not applicable
檢視作業記錄View job history 不適用Not applicable 不適用Not applicable Yes Yes 不適用Not applicable 不適用Not applicable
刪除作業記錄Delete job history 不適用Not applicable 不適用Not applicable Yes No 不適用Not applicable 不適用Not applicable
附加/卸離Attach/detach 不適用Not applicable 不適用Not applicable 不適用Not applicable 不適用Not applicable 是 (僅擁有的排程)Yes (owned schedules only) 不適用Not applicable

指派多個角色給使用者Assigning Users Multiple Roles

系統管理員 (sysadmin) 固定伺服器角色的成員,可存取所有 SQL ServerSQL Server Agent 的功能。Members of the sysadmin fixed server role have access to all SQL ServerSQL Server Agent functionality. 如果使用者不是 系統管理員 (sysadmin) 角色的成員,但是是多個 SQL ServerSQL Server Agent 固定資料庫角色的成員,請記住這些角色是同心權限模型。If a user is not a member of the sysadmin role, but is a member of more than one SQL ServerSQL Server Agent fixed database role, it is important to remember the concentric permissions model of these roles. 因為較多權限的角色永遠包含較少權限的角色的所有權限,因此使用者若為多個角色的成員,會自動擁有較多權限的角色成員所關聯的權限。Because more privileged roles always contain all the permissions of less privileged roles, a user who is a member of more than one role automatically has the permissions associated with the most privileged role that the user is a member of.

另請參閱See Also

實作 SQL Server Agent 安全性Implement SQL Server Agent Security
sp_update_job (Transact-SQL)sp_update_job (Transact-SQL)
sp_update_schedule (Transact-SQL)sp_update_schedule (Transact-SQL)
sp_notify_operator (Transact-SQL)sp_notify_operator (Transact-SQL)
sp_purge_jobhistory (Transact-SQL)sp_purge_jobhistory (Transact-SQL)