How to do change tracking for data insert/update & delete

Question

some time dotnet application change data in a specific table or some time user change data from SSMS. how could i detect which application made the change and from which IP data has been changed.

please guide me full steps to achieve this. thanks

Answer 1

Hi

DECLARE @retTable TABLE (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID2 int not null
, REQUESTID INT
)

INSERT INTO @retTable EXEC sp_who2

SELECT Status, Login, HostName, DBName, Command, CPUTime, ProgramName -- *
FROM @retTable
--WHERE Login not like 'sa%' -- if not interested in sa
ORDER BY Login, HostName

Best Regards.

Please click the Mark as answer button and vote as helpful if this reply solves your problem.

Answer 2

You can get the application name with the app_name() function. Note however, that this something that the application sets in the connection string. A .Net application will by default only say ".Net SqlClient provider" or something equally generic.

You can get the host name with the host_name() function. Again, this is something that the application can set in the connection string, so you cannot rely on it as someone might purposely spoof it. But at least by default it will be set to the user's machine name.

It is possible to get the IP-address from sys.dm_exec_connections, but this requires VIEW SERVER STATE permission, so it is not practically useful.

Answer 3

Hi @ SudipBhatt-9737,

You can try to use the SqlDependency object. A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved. It uses the Service Broker to receive messages from the database engine.

Please refer to the following articles which might help:
How to monitor SQL Server table changes by using c#?
Detecting Changes with SqlDependency
ServiceBrokerListener

Best Regards,
Amelia