3

I'm using Service Broker to [maybe] monitor my database events, so I have my activation sp in some user database and it should react on some events by sending e-mail to admin. But it fails with the error

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo

while trying to use sp_send_dbmail.

How can I manage the permissions in my case?

sepupic
  • 10,969
  • 15
  • 26
user3209649
  • 31
  • 1
  • Did you see this question? – SqlWorldWide Sep 13 '17 at 13:14
  • 1
    @SqlWorldWide this question has to do with service broker sending mail and the "duplicate" of which it isn't has to do with a DDL trigger setup by the DBA to audit. Big difference in answers, in fact the answer has no meaning to this question. – Sean Gallardy Sep 13 '17 at 15:13
  • @SeanGallardy I did not mark it as duplicate. – SqlWorldWide Sep 13 '17 at 15:15
  • 1
    Again, not a dupe. Here the user specified in EXECUTE AS in the Queue Activation configuration specified in (CREATE QUEUE)[https://learn.microsoft.com/en-us/sql/t-sql/statements/create-queue-transact-sql], or the EXECUTE AS on the activation proc (if not 'SELF'), needs permissions on sp_send_dbmail. – David Browne - Microsoft Sep 13 '17 at 17:24
  • Upvoted the comments stated this is not a duplicate. The "solution" from "dup" is: "confirm that there are no attempted email sends from your script". Why does he confirm it? His activation sp DOES send mail, and he need a solution how to make it functional...I think the only option is to sign the proc with the certificate or some variation of this – sepupic Sep 13 '17 at 18:40
  • sepupic, @DavidBrowne-Microsoft It may not be a duplicate but it's a lousy phrased question, no doubt. Can someone (who understands wht this is asking, because I'm not) edit the question and make it clear? – ypercubeᵀᴹ Sep 14 '17 at 10:05
  • @ypercubeᵀᴹ , I perfectly understand his problem but I never edited the questions that are not mine before. I'll try, please, get a look at my try – sepupic Sep 14 '17 at 10:08
  • @sepupic, sure go ahead. We can always re-edit and improve. – ypercubeᵀᴹ Sep 14 '17 at 10:14
  • @sepupic looks good. I don't understand where you got the last paragraph ("I understand that I need the user to be a member of DatabaseMailUserRole but I don't want to map anyone to my msdb and I will never add guest to this role."), were there some comments removed? Anyway, it's much better now. – ypercubeᵀᴹ Sep 14 '17 at 10:23
  • @ypercubeᵀᴹ No, there was no comments. It's the problem itself, because the activation proc is executed under the user, not a login, this user must be specified when you create the queue. So even under dbo, you cannot reach msdb if your db is not TRUSTWORTHY. But you are right, maybe it's better to remove this part at all – sepupic Sep 14 '17 at 10:30
  • @sepupic yeah, that part would probably be better in an answer. – ypercubeᵀᴹ Sep 14 '17 at 10:34
  • @ypercubeᵀᴹ and others: this is a duplicate of Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?. The security context of the activation procedure is solely at the DB level, so the only option is for it to act as guest if TRUSTWORTHY is OFF (highly recommended). The only way to keep that setting off and not need to grant permissions to guest is to use SQLCLR as I demonstrate in the linked duplicate. FWIW, this is the 2nd trickiest use case I've found in my efforts to avoid Impersonation and TRUSTWORTHY ;-). – Solomon Rutzky Sep 14 '17 at 14:03
  • @ypercubeᵀᴹ and user3209649 (and others, though nobody else will get notified of this comment): I was wrong about SQLCLR being the only method of getting this to work without resorting to TRUSTWORTHY ON. I was able to get it working in pure T-SQL via module signing once I learned about the minor yet critical option I was missing from my original attempt at this. I have updated my answer to that linked, duplicate question with the explanation and the fully working example code. – Solomon Rutzky Sep 25 '17 at 20:51

0 Answers0