November 12, 2009 at 1:31 pm
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'.'
November 26, 2009 at 11:41 pm
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]
December 3, 2009 at 9:06 am
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.
December 3, 2009 at 12:12 pm
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