0

I have a vb.net 4.6.1 Windows Forms application. In it, a SQL stored procedure is called. That stored procedure then deletes a record from a table. This is all working fine. That table has a delete trigger that sends an e-mail. That trigger fails because of a permissions issue to the msdb database. What's going on here? I'm guessing the trigger is trying to use the app user's SQL user to send e-mail. I don't want to set up every user in the msdb database. I want the trigger to run and send an e-mail no matter what user causing the trigger to fire. Is there a way for the trigger to send e-mail without using the app user's permission?

Here's the error message:

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

Here's the vb.net code. It just calls the stored procedure:

            Dim cmd As SqlCommand = MSSQL_CONN.CreateCommand

            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_cancel_backlog_detail"
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = backlog_detail_id

            Call cmd.ExecuteNonQuery()

Here's the connection string the app is using:

SERVER=sql01;DATABASE=MyDatabase;Integrated Security=SSPI;Connect Timeout=10;Pooling=false;MultipleActiveResultSets=True;

Here's the stored procedure. Again, pretty simple in that it just deletes a record form the table:

CREATE PROCEDURE [dbo].[sp_cancel_backlog_detail] @id int

DELETE FROM backlog_details WHERE id = @id;

Here's the delete trigger on the table. This is where the e-mail is sent:

ALTER TRIGGER [dbo].[delete_backlog_details] ON [dbo].[backlog_details]
AFTER DELETE
AS
BEGIN

DECLARE @body NVARCHAR(MAX)
DECLARE @subj VARCHAR(255), @rcpt VARCHAR(MAX), @atch VARCHAR(255), @q VARCHAR(255), @profile VARCHAR(255)

          SET @rcpt = 'xxxxx'
          SET @subj = 'Deleted'

          SELECT @profile = value
          FROM dishbooks.dbo.settings
          WHERE name = 'event_manager.dbmail_profile'

          EXEC msdb.dbo.sp_send_dbmail 
              @execute_query_database = 'MyDatabase', 
              @profile_name = @profile, 
              @blind_copy_recipients = @rcpt, 
              @subject = @subj, 
              @body = @body, 
              @body_format = 'HTML'

END
boilers222
  • 1,901
  • 7
  • 33
  • 71
  • 3
    If you want to send an email in a trigger, you have a design flaw, honestly. Triggers should be as minimal as possible. Put the details of the email to be sent in a "queue" table and send the email with a different process. Otherwise, if the email fails, so does the trigger, and thus so does the `DELET`. – Thom A Jul 23 '20 at 14:12
  • 1
    Also, this trigger is severely flawed, as it assumed that someone will only ever `DELETE` a single row at a time. A `DELETE` can effect 0+ rows. – Thom A Jul 23 '20 at 14:13
  • To add to @Larnu comments: dbmail is intended for alerting the database administrator and not for application/user. You can build emailing into your application using the SMTP: https://www.tutorialspoint.com/vb.net/vb.net_send_email.htm – Pete -S- Jul 23 '20 at 15:06
  • Please ... for the sake of all that is good and right in the world, move this logic out of the trigger. Put it in the stored procedure ... put it in the application ... anywhere but a trigger. – Jason Jul 23 '20 at 15:12

0 Answers0