Sql-server – Calling sp_start_job from a stored procedure

sqlsql serversql-server-2005

Our developers need to be able to start a SQL Server Agent job from their .Net code. I know I can call msdb..sp_start_job to do just that, but I don't want to give general user accounts direct access to run jobs.

What I'd like to do is to create a stored procedure in the application's database using the WITH EXECUTE AS clause to impersonate a proxy account. The procedure as we have it is:

CREATE PROCEDURE dbo.StartAgentJob 
    WITH EXECUTE AS 'agentProxy'
AS
BEGIN
    EXEC msdb.dbo.sp_start_job N'RunThisJob';
END

When we run this, though, we get the following message:

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Any ideas? Is this even the best way to do this in SQL2005?

Best Answer

Have you put the agentProxy login in the msdb database and given it rights to run sp_start_job? If not you'll need to enable database permission chaining for the msdb database and your user database.

You are probably better off putting the login into the msdb database and granting it the correct rights.

Related Topic