There are some new database roles in the MSDB database in SQL Server 2005 that allow you to grant more granular permission to  non-sa users in the SQL Server agent.

If you work in an environment similar to the one i support, application and database code will pass through several environments en-route to production. Starting on the Dev box then moving to system testing and then to user acceptance testing, if you have any SQL Agent jobs that relate to the database being tested but you don’t want these jobs to run on to run on schedule, instead you want your testers to run as and when required. You can use these new roles to give your developers and testers permissions to run the jobs when they need too.

There are three roles in MSDB in SQL Server 2005 and onwards called:

SQLAgentUserRole – which is the least privileged of the three roles. Users in this role have permission on jobs and schedules that they own. This was the role i needed in my case.

SQLAgentReaderRole – Has all the permissions of SQLAgentUserRole plus the ability to view a list of multi-server jobs.

SQLAgentOperatorRole - is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

This information is freely available in Books Online (BOL)

When a non SA user logs into an instance using SSMS, they have to be a member of one of these roles to have the SQL Agent available to them.

The following script grants my login called test access to the MSDB database and then adds it to the SQLAgentUserRole:

USE [msdb]
GO
CREATE USER [Test] FOR LOGIN [Test]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'Test'
GO

This allowed the testers to run jobs that they owned when it suited them.