http://giladka8.blogspot.sg/2012/05/running-sql-server-agent-jobs-on-demand.html
PROBLEM
You configure some SQL Server Agent jobs to run schedule maintenance tasks, but there is sometimes the need for these tasks to be executed on demand.You want other users just to run the job, without modifying it.
For those users SQL Server Agent node in Object Explorer is not visible because they have minimum permissions (no SQLAgentOperatorRole)
SOLUTION
Use database impersonation by using EXECUTE AS
USE
[master]
GO
CREATE
LOGIN
[runSqlAgentJobsLogin]
WITH
PASSWORD
=
N'123%123'
,
DEFAULT_DATABASE
=
[master]
,
CHECK_EXPIRATION
=
OFF
,
CHECK_POLICY
=
ON
GO
USE
[msdb]
GO
CREATE
USER
[runSqlAgentJobsLogin]
FOR
LOGIN
[runSqlAgentJobsLogin]
GO
EXEC
sp_addrolemember
N'SQLAgentOperatorRole'
,
N'runSqlAgentJobsLogin'
GO
CREATE
PROCEDURE
[RUN_BACKUP_SP]
WITH
EXECUTE
AS
'runSqlAgentJobsLogin'
AS
EXEC
sp_start_job
@JOB_NAME
=
'My_Backup'
GO
GRANT
EXECUTE
ON
[MSDB]
.
[DBO]
.
[RUN_BACKUP_SP]
TO
[DOMAIN\User]
GO
Now, connect as an unauthorized user to one of the databases and run the following. The user can only run the procedure that runs the job, he cannot see the job or modify it
EXEC
[MSDB]
.
[DBO]
.
[RUN_BACKUP_SP]