Sql-server – SQL Server Agent not observing “execute as” permissions

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

• The TRUSTWORTHY Database Property Explained – Part 1
• The TRUSTWORTHY Database Property Explained – Part 2
• The TRUSTWORTHY Database Property Explained – Part 3
• TRUSTWORTHY Database Property
• KB #2183687 : Guidelines for using the TRUSTWORTHY database setting in SQL Server

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

1. Create the procedure in msdb.

2. Create a user for this user's login in msdb:

   USE msdb;
   GO
   CREATE USER floobarama FROM LOGIN floobarama;
   

3. Grant the user execute privileges on the stored procedure:

   GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;
   

4. Test it - either by calling the procedure from another database:

   USE tempdb;
   GO
   EXECUTE AS LOGIN = N'floobarama';
   GO
   EXEC msdb.dbo.RunJob @job_name = N'whatever';
   GO
   REVERT;
   

   Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

   USE msdb;
   GO
   CREATE PROCEDURE dbo.whatever
   WITH EXECUTE AS N'sysadminaccount'
   AS
   BEGIN
     SET NOCOUNT ON;
     SELECT [I am really...] = SUSER_SNAME();
   END
   GO
   GRANT EXECUTE ON dbo.whatever TO floobarama;
   
   USE tempdb;
   GO
   EXECUTE AS LOGIN = N'floobarama';
   GO
   EXEC msdb.dbo.whatever;
   GO
   REVERT;
   

   Result should be:

   I am really...
   ---------------
   sysadminaccount
   

5. Validate that this doesn't expose anything else in msdb to this user:

   USE tempdb;
   GO
   EXECUTE AS LOGIN = N'floobarama';
   GO
   SELECT job_id FROM msdb.dbo.sysjobs;
   GO
   REVERT;
   

   Result should be...

   Msg 229, Level 14, State 5, Line 21
   The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

   ...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

Indeed what I was looking for (but were unaware of its name) is called cross database ownership chaining.

By reading the article Understanding Cross Database Ownership Chaining in SQL Server I was able to solve my problem.

As a reference, below is what I did:

Use [DatabaseA]
GO

ALTER DATABASE [DatabaseA]
SET DB_CHAINING ON
GO

Use [DatabaseB]
GO
ALTER DATABASE [DatabaseB]
SET DB_CHAINING ON
GO

EXEC sp_grantdbaccess 'UserB';
GO 

GRANT SELECT, UPDATE on [DatabaseB].[dbo].[TableA] TO [UserB]
GO

Thank you all.