title: sql server execute as permission errors in trigger
body: <p>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.</p>
  
  <pre><code>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='testemail@test.com',
    @body = 'This is a test for Database Mail.',
    @body_format = 'TEXT',
    @subject = 'Database Mail Test'
  END 
  GO
  </code></pre>