I'm trying to write a trigger that will inform me via an email using sp_send_dbmail when a certain value has been set on a field either by update or insert by an application a colleague has written for me. If you see the code below, it all looks syntactically correct as far as I know (although this is probably the first trigger I've ever written so may be wrong).
CREATE TRIGGER dbo.board_batch_UPDATE
WITH EXECUTE AS OWNER
IF (select count(*) from boardbatch where graphgroup = 'BAR') = 2
Exec msdb.dbo.sp_send_dbmail @profile_name = 'DRU Work Mail', @recipients = 'firstname.lastname@example.org', @subject = 'More than one with graphgroup BAR in boardbatch',
@Body = 'Run select * from boardbatch where graphgroup = BAR'
When I run the application and set another graphgroup to BAR I get the following error:
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'
On researching this error, I've seen this suggested quite regularly:
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '[User1]';
where User1 is the username being used by the application to run the updates. Having added the user to the msdb database and run the above statement, I still get the same error.