What Permissions are needed for a Queue-activated Procedure?

  • I'm stumped. I've added the User 'TestDBEngine' to the DatabaseMailUserRole. The User is not a member of any other Role in MSDB, and owns no Schemas in MSDB. What other Permission have I missed? Do I need to grant EXECUTE permission for sp_send_dbmail?

    CREATE QUEUE Deadlock_Graph_Queue

    WITH STATUS = ON,

    RETENTION = OFF,

    ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.s_DBA_Deadlock_Graph_Email,

    MAX_QUEUE_READERS = 1,

    EXECUTE AS 'TestDBEngine')

    The activated proc [dbo].[s_DBA_Deadlock_Graph_Email] running on queue TRSDev.dbo.Deadlock_Graph_Queue output the following: 'The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.'

  • You may need to mark your database as TRUSTWORTHY.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks, Barry! That was, indeed, another piece of the puzzle. Glad to see you're 'back' (pun intended, since we share that particular human fraility...)

    I finally had to give up on that particular approach, due to time constraints. On another server, used by the DBA group for various ad-hoc and ongoing processes, we already had DBMail set up with permissions for the SQLServerAgent login. I had previously set up a 'deferred email' scheduled SQL Server Agent Job, which would send emails based on tabled data. I 'punted', and created an 'indirect email' applet, which allows any SQL Server instance to store email information in a Table. An 'Insert' trigger on the Table starts the relevant SQL Server Agent Job to process the Table's entry/entries.

    While it's just a bit cumbersome, it also allows me to generate emails on any SQL Server, without resorting to multiple incarnations of DBMail and the attendant maintenance / security / paperwork, etc. For DBA purposes, we already have established linked server relationships, so that wasn't a factor. The only thing I have to change is the 'Trustworthy' attribute on the source servers, so your reply was *not* 'in vain'.

    Glad to see you back on 'The Thread', BTW.

  • Glad I could help, Phil. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply