Sql-server – Database role to allow .NET program to retrieve MSDB table and view rows

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).

This is an issue of ownership chaining and how that doesn't automatically translate across databases as most people would expect. The following MSDN page has a good explanation of what is going on in this situation:

Ownership Chains

That article states that in order to get this to work as most would expect, you need to enable "Cross-Database Ownership Chaining". However, this is a security risk, and that article even has a section for "Potential Threats".

The more secure way to extend permissions is by using module signing. Please see my answer on the following Question (here on DBA.SE) for how to remedy this, especially if you don't want to give the user any access to Database B:

stored procedure can select and update tables in other databases - minimal permissions granted

The first time you go through this exercise it might seem terribly complicated, but after a few times it will make enough sense to not seem to bad ;-).