Sql-server – What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change

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

With Amazon RDS, you don't get direct access to the underlying filesystem.

If you have an existing database that you want to migrate into RDS SQL Server, rather than using the MDF/LDF files, you'll need to do a restore instead.

Amazon's instructions to restore a database are pretty good, but since you're asking this question, I'm guessing I probably need to fill you in on a background concept. Remember how I said you don't get access to the filesystem? Well, you can get access to OTHER file systems, kinda like how mapped drives work in Windows. S3 is Amazon's simple storage service where you can create buckets and put files in 'em. You're going to need to:

• Create an S3 bucket
• Put your database backup (not MDF/LDF) in the bucket
• Give your RDS instance permission to read from that S3 bucket (that's where Amazon's instructions come in)
• Use Amazon's custom stored procedure rds_restore_database to restore the backup