sql server execute as permission errors in trigger

Question

I have a trigger where I want to send out an email on updates to a row in a table SalesClosing. Now the user (dbuser) who execute the trigger has very limited permissions. So I want the trigger to execute as dbmailuser. A user who has rights to send out email. I tested that dbmailuser can execute sp_send_dbmail when logged in as that user. When the trigger fires though, I get an error can't execute sp_send_dbmail. So I logged in as dbuser , ran EXECUTE AS LOGIN = 'dbmailuser'; and was able to execute sp_send_dbmail. Now why can't I do that in a trigger. I'm using sql server 2008. Below is the trigger ddl.

alter TRIGGER SalesClosingTrigger ON SalesClosing
WITH EXECUTE AS 'dbmailuser'
for insert, update
AS
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  --@profile_name = 'Test_Email_Profile',
  @recipients='[email protected]',
  @body = 'This is a test for Database Mail.',
  @body_format = 'TEXT',
  @subject = 'Database Mail Test'
END 
GO

Answer 1

This is due to execute-as-user scoping - context switching to a database-level user is by default scoped to only that database (and the code above is executing outside the given database across to msdb), and the database-only authenticator is invalid. See the link above for multiple options on how to resolve/work-around.

Answer 2

The triggers's EXECUTE AS is the same as EXECUTE AS USER = '...', not the same as EXECUTE AS LOGIN = '...'. chadhoc already pointed out the link to the EXECUTE AS impersonation context and its constraints. Basically, because the trigger's EXECUTE AS clause is guaranteed by dbo, not by sysadmin, it is trusted only inside the context of the database.

There are two alternatives:

1. The one size fits all sledgehammer: ALTER DATABASE <yourdb> SET TRUSTWORTHY ON;. This will elevate mark the database as trusted and the execution context can go outside the database, if the loggin that owns the database has the propper rights. This is not recommended on a highly secured environement as it opens the doors to various elevation of priviledges if not properly constrained, and is very difficult to properly constrain.

2. The surgical precission option: code signing. See Call a procedure in another database from an activated procedure for an example. This is not for the faint of heart, it involves several complex steps: generate a certificate, sign the procedure, drop the private key, copy the certificate into msdb, create an user derived from the certificate in msdb, grant authenticate database on the certificate derived user, grante EXECUTE on sp_send_mail on the certificate derived user. Any mistake at any of these steps will render the whole sequence useless so is very easy to screw it up, but the result is absolute bulletproof from a security point of view.

Answer 3

Sending an email from a trigger is a bad idea. You don't want to be unable to make data changes if the email server is down.

It is better to send the info for the email to another table which then sends the email from a periodically running job. One that runs say every five minutes.

There is also something called Service Broker that I've not used before that can help out in this task, you might want to look at that.