I am working with a database in MS-SQL 2008 R2, and I'm running into a problem.
The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the working data table, and has triggers for data changes (insert, update, delete) to update audit.Assesments with a log of changes. As far as I can tell, this has worked for years, so far so good.
Our L2 support has a Role called SupportRole that they get into with WinAuth, and that role has some select rights and execute rights for stored procedures. One of those stored procedures does an update to dbo.Assesments. This is where the problem comes in: The update goes in, the stored procedure fires to insert a audit row to audit.Assesments, and then they promptly get a "Insert denied, insufficient rights to Insert", the transaction roles back, etc.
This is mystifying to me: I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing, but this has me thinking twice. I don't want to give insert rights to the support role for the audit table/schema. What should I do?