1

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?

Improve this question
10
  • Have you compared the server level security for the Logins on the old server to the new server, especially the one's that pertain to your accounting staff?
    – J.D.
    Commented Oct 17, 2022 at 11:55
  • UPDATE I found my user, and a colleague were members of an AD group (SQL Admins) which has SYSADMIN role. When I remove that it fails for me also. So I know that will work BUT I don't have all staff on SYSADMIN on the Cluster where it is working
    – David Mapes
    Commented Oct 17, 2022 at 12:01
  • Right, I suspected it was a group disparity. Verify closely all AD Groups (including groups that are children of other groups on the AD side), SQL Roles, and the individual Logins themselves on the old server.
    – J.D.
    Commented Oct 17, 2022 at 12:07
  • Thanks JD. It's an odd one, not helped by how NAV pushes its own security objects into SQL. Now trying to work out how I can see what is different. 2008 Server User A 1) Read/Write to NAV DB (default schema is their name - which is what NAV does) 2) Access to BI Database via an AD Group (Read/Write) (no default schema) 2017 Server 1) Read/Write to NAV DB (default schema is their name - which is what NAV does) 2) Access to BI Database via an AD Group (Read/Write) (no default schema) 2017 Cluster (Different DBs) 1) Access to ERP & BI via AD Group (same as on 2017 above)
    – David Mapes
    Commented Oct 17, 2022 at 12:47
  • Does anyone know if there is a way to check on the permissions in SQL to list each DB and what / how they gain access? I've tried SELECT default_database_name, [UserName] = ulogin.[name], [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.type_desc ELSE perm.[class_desc] END, [ObjectName] =
    – David Mapes
    Commented Oct 17, 2022 at 12:59

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.