boilers222
boilers222

Reputation: 1989

Why doesn't a SQL trigger have permissions to use e-mail?

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

Upvotes: 0

Views: 43

Answers (0)

Related Questions