I've created a very simple INSERT trigger (attached) to send an email to myself and one other user whenever a new record is added to a certain table by anyone who uses the database (they add new records via an application called EBMS). The problem is: the email only gets sent if the users login is either a member of the System Administrators server role, or is listed as db_owner of the database i.e. when I add a record it works fine. When 'normal' public users add a record the email doesn't get sent.
I thought this might be because the 'normal' users didn't have execute permissions to the xp_sendmail stored procedure in the Master database, but I tried giving one login this permission (gave the login access to the Master db first), but when that login added a record to the application database the mail didn't get sent. Any help much appreciated.