Reputation: 1989
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