I am trying to create a process where an email is sent on data changes to specified tables within the application. After creating the trigger, I consistently get an 'Access Denied' when altering table from the sharepoint app. DB Mail seems to be functioning from the SQL server itself, I am able to send and receive the test emails. I pulled the error log, and here's what I believe is the pertinent snippet:
Message : Established SQL security context. Database User: 'AccessWriter'.
Message : CommandType=Text; CommandText=CREATE TABLE #Context (ID INT PRIMARY KEY, isTopLevel BIT) INSERT INTO
#Context VALUES (1,1) UPDATE [Access].[tbl_workorders] SET [Customer Stated Issue] = @p0 WHERE [ID] = @p1
AND [Work Order Number] = @p2 AND [Customer ID] = @p3 AND [Employee ID] = @p4 AND [Asset ID] = @p5 AND
[Description of Work] = @p6 AND [Date Performed] = @p7 AND [Quickbooks Invoice] IS NULL AND [Asset ID 2] IS
NULL AND [Asset ID 3] IS NULL AND [Workorder Agreement] IS NULL AND [Customer Stated Issue] = @p8 AND
[Date Submitted] = @p9 AND [Invoice Total] = @p10 AND [Payment Notes] IS NULL AND [Payment Type] = @p11 AND
[ID] = @p12 SELECT @@rowCount SELECT [tbl_workorders].* ,[tbl_customers:Customer ID->ID].[Customer Name]
AS [Customer ID:tbl_customers:ID:Customer Name], [tbl_e...
Message : System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object
'sp_send_dbmail', database 'msdb', schema 'dbo'. at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1
wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader
dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at
System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.Fini...
I have added the db mail role to the user accounts associated with the sharepoint instance, and also tinkered with the permissions on the database role 'AccessWriter' to no avail.