MS SQL 2005: Configure a User to Create and Manage SQL Server Agent Jobs

I was looking for a way to assign a role for a user to create and execute jobs that account created, and I found a way thru the MSDN library online.  Link can be found here, which the article shows how to add a role for a user that can manage the ms sql agent only.

Here is a copy of the article, in case the site goes down or the link has been changed:

This topic describes how to configure a user to create or execute Microsoft SQL Server Agent jobs.

--------------------------------------------------------------------------------------------------------

Before You Begin:

Security

To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL 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. If these non-administrative users want to run jobs that execute other job step types (for example, SSIS 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 SQL Server Agent fixed database roles, see SQL Server Agent Fixed Database Roles.

Permissions

For detailed information, see Implement SQL Server Agent Security.

-------------------------------------------------------------------------------------------------------

*Now to the fun part, the actual act of configuring the user rights:

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 SQL Server 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 SQL Server Agent fixed database role.

*I only reached until step 5, and never bothered with configuring a proxy account.

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 Create a Credential 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.

Other things concerning Agent Roles can be found on this link: http://msdn.microsoft.com/en-us/library/cc966397.aspx


After the initial installation of SQL Server, only the System Administrator (sysadmin) role has access to view, modify, create, and execute SQL Server Agent jobs. The Agent node in SQL Object Explorer is available only to users who are assigned the System Administrator role. To grant a user access to SQL Server Agent, you must add the user to one of the two new database roles in the msdb database.

New Roles in the msdb Database

SQL Server Agent in SQL Server 2005 adds three new roles to the msdb database:

• SQLAgentUserRole Users added to the SQLAgentUserRole role will have the same SQL Server Agent experience as they had in SQL Server 2000. These users can create jobs and manage only jobs that they created.
• SQLAgentReaderRole Users added to the SQLAgentReaderRole role will have the same privledges as those in the SQLAgentUserRole with the addition of the ability to enumerate and view the history of all jobs..
• SQLAgentOperatorRole Users added to the SQLAgentOperator role will have the same privledges as those in the SQLAgentReaderRole with the addition of the ability to execute local jobs that they do not own.

Multiple Proxy Accounts

In SQL Server 2000, when a user wanted to execute a SQL Server Integration Services (SSIS) package as a job step, the sysadmin had to enable the SQL Server Agent proxy account. After the account was enabled, any SQL Server Agent user could create job steps for certain subsystems, such as CmdExec, and run them by using this account. In SQL Server 2005, SQL Server Agent addresses this security concern by using multiple proxy accounts.
By default, in the new release of SQL Server Agent, only sysadmins can create job steps of the following types:

• Active Scripting
• SQL Server Integration Services Package
• Analysis Command
• Analysis Query
• All replication subsystems

Transact-SQL subsystems will execute under the owner just as they did in SQL Server 2000.
To grant users other than sysadmins the right to create a job step for types other than Transact-SQL, a sysadmin must create at least one proxy account for the user. This proxy account is simply a credential with a friendly name.
Credentials must be created before Agent proxy accounts can be created. To create a credential, use the following script:

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = '<domain>\<user>', secret = '<password>'

Note: Although the example stated in this paper use T-SQL to create credentials and proxy accounts, SQL Server Management Studio provides a series of dialogs to aid in the creation of these objects.
To add a proxy account, use the following script:

Sp_add_proxy @proxy_name='My Proxy', @credential_name='MyCredential'