Skip to content

Latest commit

 

History

History
74 lines (50 loc) · 4.88 KB

configure-a-user-to-create-and-manage-sql-server-agent-jobs.md

File metadata and controls

74 lines (50 loc) · 4.88 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Configure a User to Create and Manage SQL Server Agent Jobs
Configure a User to Create and Manage SQL Server Agent Jobs
markingmyname
maghan
01/19/2017
sql
ssms
how-to
SQL Server Agent jobs, user configuration
jobs [SQL Server Agent], user configuration
SQLAgentUserRole database role
proxy accounts [SQL Server Agent]
= azuresqldb-mi-current || >= sql-server-2016

Configure a User to Create and Manage SQL Server Agent Jobs

[!INCLUDE SQL Server SQL MI]

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

This topic describes how to configure a user to create or execute [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Agent jobs.

Before You Begin

Security

To configure a user to create or execute [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following [!INCLUDEssNoVersion] 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. If these non-administrative users want to run jobs that execute other job step types (for example, [!INCLUDEssIS] packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts. For more information about the permissions that are associated with these [!INCLUDEssNoVersion] Agent fixed database roles, see SQL Server Agent Fixed Database Roles.

Permissions

For detailed information, see Implement SQL Server Agent Security.

Using SQL Server Management Studio

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. Right-click the login you wish to add to a [!INCLUDEssNoVersion] Agent fixed database role, and select Properties.

  4. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.

  5. Under Database role membership for: msdb, check the appropriate [!INCLUDEssNoVersion] Agent fixed database role.

To configure a proxy account to create and manage SQL Server Agent job steps

  1. In Object Explorer, expand a server.

  2. 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. Note that you must create a credential first before creating a SQL Server Agent proxy. For more information about creating a credential, see How to: Create a Credential and CREATE CREDENTIAL (Transact-SQL).

  5. Check the appropriate subsystems for this proxy.

    1. Operating system (CmdExec)
    2. SQL Server Analysis Services Query
    3. SQL Server Analysis Services Command
    4. SQL Server Integration Services Package
    5. PowerShell

  6. On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

See Also