A customer's database loses data from one specific table when a particular user logs on to the Application. I have created a trigger on that table to log various bits of information as How, when why etc. The trigger includes this
DECLARE @LASTCOMMAND NVARCHAR(max)
--get he last command by the current spid:
SELECT @LASTCOMMAND = DEST.TEXT
FROM sys.[dm_exec_connections] SDEC
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle])
WHERE SDEC.[most_recent_session_id] = @@SPID[/size]
This work ok when the table is updated from Management Studio, but fails when the table is updated via the application with a message about not having permissions to do this. Can anyone advise on permisions please?