Configure a User to Create and Manage SQL Server Agent JobsConfigure a User to Create and Manage SQL Server Agent Jobs

适用对象: yesSQL ServeryesAzure SQL 数据库(仅限托管实例)noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse


Azure SQL 数据库托管实例目前支持大多数但并非所有 SQL Server 代理功能。On Azure SQL Database Managed Instance, most, but not all SQL Server Agent features are currently supported. 有关详细信息,请参阅 Azure SQL 数据库托管实例与 SQL Server 之间的 T-SQL 差异See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details.

本主题介绍如何对用户进行配置以创建或执行 MicrosoftMicrosoft SQL ServerSQL Server 代理作业。This topic describes how to configure a user to create or execute MicrosoftMicrosoft SQL ServerSQL Server Agent jobs.

开始之前Before You Begin


若要配置用户以创建或执行 MicrosoftMicrosoft SQL ServerSQL Server 代理作业,必须先将某个现有 SQL Server 登录名或 msdb 角色添加到 msdb 数据库中的下列 SQL ServerSQL Server 代理固定数据库角色之一:SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole。To configure a user to create or execute MicrosoftMicrosoft SQL ServerSQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL ServerSQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

默认情况下,这些数据库角色的成员可以创建各自的作业步骤,这些作业步骤不执行其他作业步骤。By default, members of these database roles can create their own job steps that run as themselves. 如果这些非管理用户要运行那些执行其他作业步骤类型(例如, SSISSSIS 包)的作业,它们需要对代理帐户具有访问权限。If these non-administrative users want to run jobs that execute other job step types (for example, SSISSSIS packages), they will need to have access to a proxy account. sysadmin 固定服务器角色的所有成员都有创建、修改和删除代理帐户的权限。All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts. 有关与这些 SQL ServerSQL Server 代理固定数据库角色相关的权限的详细信息,请参阅 SQL Server 代理固定数据库角色For more information about the permissions that are associated with these SQL ServerSQL Server Agent fixed database roles, see SQL Server Agent Fixed Database Roles.


有关详细信息,请参阅实现 SQL Server 代理安全性For detailed information, see Implement SQL Server Agent Security.

使用 SQL Server Management StudioUsing SQL Server Management Studio

将 SQL 登录帐户或 msdb 角色添加到 SQL Server 代理固定数据库角色To add a SQL login or msdb role to a SQL Server Agent fixed database role

  1. 对象资源管理器中,展开某个服务器。In Object Explorer, expand a server.

  2. 展开 “安全性” ,然后展开 “登录名”Expand Security, and then expand Logins.

  3. 右键单击要添加到 SQL ServerSQL Server 代理固定数据库角色的登录帐户,然后选择“属性” 。Right-click the login you wish to add to a SQL ServerSQL Server Agent fixed database role, and select Properties.

  4. “登录属性” 对话框的 “用户映射” 页上,选择包含 msdb的行。On the User Mapping page of the Login Properties dialog box, select the row containing msdb.

  5. “数据库角色成员身份: msdb” 下,选中适当的 SQL ServerSQL Server 代理固定数据库角色。Under Database role membership for: msdb, check the appropriate SQL ServerSQL Server Agent fixed database role.

配置代理帐户以创建和管理 SQL Server 代理作业步骤To configure a proxy account to create and manage SQL Server Agent job steps

  1. 对象资源管理器中,展开某个服务器。In Object Explorer, expand a server.

  2. 展开 “SQL Server 代理”Expand SQL Server Agent.

  3. 右键单击“代理” ,再选择“新建代理” 。Right-click Proxies and select New Proxy.

  4. “新建代理帐户” 对话框的 “常规” 页上,指定新代理的代理名称、凭据名称和说明。On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. 请注意,在创建 SQL Server 代理的代理帐户之前,必须先创建一个凭据。Note that you must create a credential first before creating a SQL Server Agent proxy. 有关创建凭据的详细信息,请参阅如何:创建凭据 (SQL Server Management Studio)CREATE CREDENTIAL (Transact-SQL)For more information about creating a credential, see How to: Create a Credential (SQL Server Management Studio) and CREATE CREDENTIAL (Transact-SQL).

  5. 检查此代理的相应子系统。Check the appropriate subsystems for this proxy.

  6. “主体” 页上,添加或删除登录名或角色,以授予或删除对代理帐户的访问权限。On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

另请参阅See Also

实现 SQL Server 代理安全性Implement SQL Server Agent Security