SQL Server How to find out who update table data

Question

I have table which can be updated by user who login from SSMS or a .net application can update data in table.

A .net application can execute in-line query from there to update table or a .net application can call a store procedure to update data in table. how do i find out who update data in table just by running any script.

please tell me few ways to extract this info from database who update a particular table.

1. if a user update table from SSMS then i need user login name
2. if any .Net application update data in table then i should get .net application name.

Please share the script or any other way to extract this info from database who update a particular table.

thanks

Edit

Some links i got after searching google
https://www.sqlshack.com/track-history-data-changes-using-sql-server-2016-system-versioned-temporal-tables/
https://stackoverflow.com/questions/29834515/how-to-find-who-last-modified-the-table-in-sql-server
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/160680a3-3f9f-43b1-a689-91943461dda7/how-to-find-which-loginuser-has-modified-the-record-in-a-table-in-sql-server-2008?forum=transactsql

Answer 1

Hi @Sudip Bhatt ,

I tried with your query and could only get the default trace which does not include who modified a row in a table from my side.

In order to find out who update the table, you could try with below options:

1. Try and read the Transaction Logs to see what happened.
2. Start trace in SQL Server profiler and checked events(TSQL-SQL:BatchCompleted,SQL:BatchStarting,SQL:StmtCompleted and SQL:StmtStarting)(Recommended).
3. Create a trigger and track the username into a log table. (SELECT APP_NAME() or SELECT program_name from sys.dm_exec_sessions where session_id=@@SPID).
4. Try with SQLAudit functionality which will give you some great granualar information about who is touching your tables, and the commands that are being executed.
5. Change Data Capture (CDC).
6. Third-part tool like ApexSQL Log.

You could refer more details in How to find who changed a table value in SQL Server 2014?.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?--- Hot issues November
Hot issues November--How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

First of all, you will need to code for this, one way or another. There is nothing built-in.

To get the user name there is a pleothora of functions, but many of them are synonyms of each other. The best to use in most cases is original_login().

To get the application name, you can use app_name(), but keep in mind that this is something that the application sets itself in the connection string.

As for setting the values, you can define as the defaults on the corresponding columns, and that works for INSERTs, but for UPDATE only if you say:

UPDATE tbl
SET     whodidit = DEFAULT,
          appname = DEFAULT
WHERE  ....

Someone updating from SSMS may not remember to do that. You can fix this with a trigger:

CREATE TRIGGER tri ON TABLE AFTER INSERT, UPDATE AS
UPDATE tbl
SET     whodidit = DEFAULT,
          appname = DEFAULT
FROM   tbl t
JOIN   inserted i ON t.keycol = i.keycol

But this comes with a cost in overhead for the extra update. This can be avoided by using an INSTEAD OF trigger, but they are more difficult to write and maintain.