Working out what application is changing an sql database value

Question

I'm running into an issue where the legacy database that many applications read/write to keeps getting changed, and I can't work out what is changing it.

My application changes a certain value in a certain row of the table, but something keeps changing it back after a week or so and I'm stumped to work out what it could be.

Is there any way I can attach an event/trigger onto this value and then have it store/email the details of what changed it ? or at least what time it was changed?

Answer 1

If you have access to connection strings for your legacy applications add Application Name keyword to connection string. Some info here.

On the server side create a trigger for the table you want to track. In the trigger log app_name() into a log table. Like this:

create trigger Tracker
on TableName
after insert, update, delete 
as
    insert LogTable(TableName, ApplicationName)
    values('TableName', app_name())
go

Also, if you have a dedicated user per application you could check and log system_user value.

If your applications hosted by different servers you can add client IP too. You can check it like this select client_net_address from sys.dm_exec_connections where session_id = @@SPID.

Also, you can use profiler to see all those parameters.

But all this with the caveat you can update your connection strings.