I have some triggers set up on a database (Dynamics NAV) across various tables for Insert, Update, Delete. The triggers write to a separate database to log the DB, Table, RowId and date/time. The triggers worked in 2008 SQL using the "WITH EXECUTE AS OWNER" command.
On another server, 2017 SQL Cluster, I have the same triggers running (different tables) without the EXECUTE AS OWNER. Everything worked fine.
We moved the Dynamics NAV database from 2008 to 2017 (separate server to the other applications as it does not support SQL Clusters). The move was a SQL.BAK export/import
Everything works OTHER than the trigger.
I've removed the EXECUTE AS to mirror the other 2017 server, but have had no luck
The Dynamics NAV DB is owned by "MyAdminAcc" and the DB it writes into (BI) is owned by the same user.
When I update the table through SSMS the trigger works When I use the application and perform an update using my standard user (not the MyAdminAcc user) it works When a colleague in IT runs the application it works When all accounting staff run, it fails (bad username/password).
The trigger seems to be running as DBO
I've looked in SECURITY for my user and the staff, they have the same access I've checked the database SECURITY for the same, again it's the same
I can't work out why this is now not working.
This is the UPDATE trigger
ALTER TRIGGER [dbo].[bi_AR_U_UK] ON [dbo].[Acme UK$Customer] AFTER UPDATE AS BEGIN SET NOCOUNT ON; insert into [BI].[trigger].[nav_arp_account] ([db], [tb], [accno], [change], [insertedon] ) select 'Dynamics_Test','Acme UK$Customer',[no_], 'Updated', GETDATE() from inserted; END
Does anyone have any thoughts?