question

SudipBhatt-9737 avatar image
1 Vote"
SudipBhatt-9737 asked MelissaMa-msft answered

SQL Server How to find out who update table data

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SudipBhatt-9737,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I got the below script.....does it help me to find out who update the table?

 DECLARE @filename VARCHAR(255) 
 SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
 FROM sys.traces   
 WHERE is_default = 1;  
    
 SELECT gt.HostName, 
        gt.ApplicationName, 
        gt.NTUserName, 
        gt.NTDomainName, 
        gt.LoginName, 
        gt.SPID, 
        gt.EventClass, 
        te.Name AS EventName,
        gt.EventSubClass,      
        gt.TEXTData, 
        gt.StartTime, 
        gt.EndTime, 
        gt.ObjectName, 
        gt.DatabaseName, 
        gt.FileName, 
        gt.IsSystem
 FROM [fn_trace_gettable](@filename, DEFAULT) gt 
 JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
 WHERE EventClass in (164) 
 ORDER BY StartTime DESC; 
0 Votes 0 ·

Depends on what you mean with "who update the table". I would understand that as "who ran an UPDATE statement against the table".

You have found a query that reads data from the default trace, which SQL Server starts when it starts up. This trace captures "interesting" events, where Microsoft has defined what is interesting.. Running an UPDATE statement against a table is not one of them.

The particular event type you are looking at, 164, is Object:Altered. This event fires if some alters an object, for instance adds or drops a column from table, adds a constraint etc. I don't know for sure, but I would expect that it is also fires if you add an index - or you rebuild it.

If this is what you had in mind, then this query may be helpful. However, data in the default trace is not saved for a very long time. There are five rollover files, and the max size is 20 MB, so you only find data maybe two days back,

0 Votes 0 ·