How to allow a user with an SQL login to manage SQL Server Agent

I was placed in an interesting situation where I needed to manage SQL Server jobs from a web application, which would sit on a completely different server and I was only allowed to use SQL Logins (not Windows logins) to perform any SQL task, I needed a way to use an SQL server login to manage SQL Server agent jobs.

NOTE: I will be writing a tutorial on managing SQL server jobs via C# but if you’d like me to put a rush on it please let me know in the comments below.

The steps needed to set up an SQL login with sufficient permissions to manage SQL Server jobs are mentioned below.

Setting up the User:

1. Run SQL Server Management Studio if not already started
2. In the object explorer expand the server (if it’s not expanded already)
3. Expand Security
4. Right click Logins and left click New Login
5. Ensure SQL Server Authentication is selected
6. Setup your username and password as you like
7. On the left pane, left click User Mapping
8. Ensure msdb is checked in the user mapped to section
9. With msdb being selected ensure the following are checked in the Database Role Member forsection
   1. SQLAgentOperatorRole
   2. SQLAgentReaderRole
   3. SQLAgentUserRole
10. Left click OK when you are done

Setting up the credential:

1. Run SQL Server Management Studio if not already started
2. In the object explorer expand the server (if it’s not expanded already)
3. Expand Security
4. Right click Credentials and left click New Credential
5. Enter your credential name and select an identity then choose your password
6. Left click OK when done

Linking the Credential and the user to SQL Server agent:

1. Run SQL Server Management Studio if not already started
2. In the object explorer expand the server (if it’s not expanded already)
3. Expand SQL Server Agent
4. Right click Proxies and left click New Proxy
5. Enter a proxy name and select a credential
6. In the Active to the following subsystems section select the following:
   1. Power Shell
   2. SQL Server Integration Services package
   3. SQL Server Analysis Servers query
   4. SQL Server analysis services command
   5. Operating System
   6. ActiveX Script
7. NOTE: not all of the above is needed, if you need tighter security you may remove some of the selected items as you need
8. On the left panel click Properties
9. Below Proxy Account principal click Add
   1. Select the Principal type of SQL Login
   2. And tick the SQL Logins you need mapped to this credential
   3. Left click OK
10. Left click OK

When you login to SQL management studio with that newly created user you will now see SQL Server Agent