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
- create a derived user from the imported certificate in
- grant AUTHENTICATE permission to the derived user in
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:
- map the impersonated user
agentProxy user in
msdb and grant him execute permission on
- grant the execute permission to the
msdb authenticator certificate derived user
- 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
Here is the corresponding SQL:
create certificate agentProxy
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
with subject = 'agentProxy'
ADD SIGNATURE TO OBJECT::[StartAgentJob]
BY CERTIFICATE [agentProxy]
WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
alter certificate [agentProxy]
remove private key;
backup certificate [agentProxy]
create certificate [agentProxy]
create user [agentProxyAuthenticator]
from certificate [agentProxy];
grant authenticate to [agentProxyAuthenticator];
grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator];
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.