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:

    WITH EXECUTE AS 'agentProxy'
    EXEC msdb.dbo.sp_start_job N'RunThisJob';

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?

  • 1
    Solved. There were three pieces to the solution: ownership chaining must be enabled on the server; the user used in the EXECUTE AS statement has to be sa or a user with similar permissions in order to run the xp_sqlagent_* jobs; and the job must be owned by the same user as is listed in the EXECUTE AS statement. Jul 14, 2009 at 15:30
  • A little more experimentation showed one variation to this solution. If you want to use a non-SA proxy user to run the job, you can grant the proxy user EXECUTE permissions on the xp_sqlagent_* procedures in the master database. (The other two requirements - cross-database ownership and job ownership - still apply.) Jul 14, 2009 at 15:44

5 Answers 5


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.

  • Yes - I started by adding it to the SQLAgentOperator role and then tried direct EXECUTE permissions on sp_start_job itself. Neither helped. It seems to throw this error regardless of the permissions of the proxy - even a sysadmin-level account fails. Jun 26, 2009 at 14:54
  • Use SQL Profiler and see what account is actually making the call. Now that I think about it more, Execute As is use a database user, which probably isn't translating out to the other database correctly. Try turning on database chaining and see if that works.
    – mrdenny
    Jun 26, 2009 at 17:20
  • The ownership chaining was a big part of the solution, so I'm awarding the points here. It also turns out that there are two other pieces to this; I'll note those above. Jul 14, 2009 at 15:28

I'm glad you solved this, but ownership chaining is not the recommended solution. Since you seem validly concerned about security and proper granularity of the rights involved, I'm adding this reply, although late, as a reference to what's happening and how to resolve this problems.

EXECUTE AS impersonation scope

The EXECUTE AS clauses come in two flavors: EXECUTE AS LOGIN and EXECUTE AS USER. The EXECUTE AS LOGIN is authenticated by the server and is an impersonation context trusted by the entire SQL instance (server-scoped):

When impersonating a principal by using the EXECUTE AS LOGIN statement, or within a server-scoped module by using the EXECUTE AS clause, the scope of the impersonation is server-wide. This means that after the context switch, any resource within the server that the impersonated login has permissions on can be accessed.

EXECUTE AS USER is authenticated by the database and is an impersonation context trusted only by that database (database-scoped):

However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

A stored procedure that has an EXECUTE AS clause will create a database scoped impersonation context, and as such will be unable to reference objects outside the database, case in point being you will not be able to reference msdb.dbo.sp_start_job because is in msdb. There are many other examples available, like trying to access a server scope DMV, trying to use a linked server or trying to deliver a Service Broker message into another database.

The enable a database scoped impersonation to access a resource that would not be normally allowed the authenticator of the impersonation context has to be trusted. For a database scoped impersonation the authenticator is the database dbo. This can be achieved by two possible means:

  • By turning on the TRUSTWORTHY property on the database that authenticated the impersonation context (ie. the database where the EXECUTE AS clause was issued in).
  • By using code signatures.

These details are described in MSDN: Extending Database Impersonation by Using EXECUTE AS.

When you resolved the issue via cross database ownership chaining you have enabled the cross-db chaining at the entire server level, which is considered a security risk. The most controlled, fine grained way to achieve the desired result is to use code signing:

  • In the application database create a self signed certificate
  • sign the dbo.StartAgentJob with this certificate
  • drop the private key of the certificate
  • export the certificate to disk
  • import the certificate into msdb
  • create a derived user from the imported certificate in msdb
  • grant AUTHENTICATE permission to the derived user in msdb

These steps ensure that the EXECUTE AS context of the dbo.StartAgentJob procedure is now trusted in msdb, because the context is signed by a principal that has AUTHENTICATE permission in msdb. This solves half of the puzzle. The other half is to actually grant the EXECUTE permission on msdb.dbo.sp_start_job to the now trusted impersonation context. There are several ways how this can be done:

  1. map the impersonated user agentProxy user in msdb and grant him execute permission on msdb.dbo.sp_start_job
  2. grant the execute permission to the msdb authenticator certificate derived user
  3. add a new signature to the procedure, derive a user for it in msdb and grant the execute permission to this derived user

Option 1. is simple, but has a big disadvantage: the agentProxy user can now execute the msdb.dbo.sp_start_job at its own will, he is truly granted access to msdb and has the execute permission.

Option 3 is positevely correct, but I feel is unnecessary overkill.

So my preffered is Option 2: grant the EXECUTE permission on msdb.dbo.sp_start_job to the certificate derived user created in msdb.

Here is the corresponding SQL:

use [<appdb>];

create certificate agentProxy 
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
    with subject = 'agentProxy'
   , start_date='01/01/2009';

      BY CERTIFICATE [agentProxy]
        WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';

alter certificate [agentProxy] 
  remove private key;

backup certificate [agentProxy] 
 to file='c:\temp\agentProxy.cer';

use msdb

create certificate [agentProxy] 
  from file='c:\temp\agentProxy.cer';

create user [agentProxyAuthenticator] 
 from certificate [agentProxy];

grant authenticate to [agentProxyAuthenticator];
grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator];

use [<appdb>];

exec dbo.StartAgentJob;

My blog has some articles covering this topic, written in the context of Service Broker activated procedures (since they require an EXECUTE AS clause):

BTW, if you're trying to test my script and you live on the eastern hemisphere, or on UK summer time, definitely read that last article I linked before testing.


Since you're trying to start SQL Server Agent from .NET code, this might be a better question for StackOverflow?


  • 1
    I think this is probably a database security issue, but I'll try StackOverflow if we can't find an answer here. Jun 26, 2009 at 14:51

Checking a random SQL Instance on the network SQLAgentOperatorRole doesn't give you sp_start_job privledges directly, it inherits them from SQLAgentUserRole .

Double check it using :

select dp.NAME AS principal_name,
                 dp.type_desc AS principal_type_desc,
                 o.NAME AS object_name,
                 p.state_desc AS permission_state_desc 
    from    sys.database_permissions p
    left    OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID
    inner   JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
    where o.name = 'sp_start_job'

Run this in MSDB and double check you havn't inherited any explicit denial access.


  • The user is explicitly a member of SQLAgentOperatorRole and SQLAgentUserRole. Jul 14, 2009 at 14:47

One way of achieving this without granting additional permissions: do not let the stored proc start the job directly, but just allow the stored proc to flip a bit in a table (in the application database); then, let the job run every minute or so, check if the bit is flipped and if so, perform the work and flip the bit back again. If the job sees the bit is not flipped, the job will just exit.

Works like a charm, if you don't mind the delay (and the job running very often).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.