How to find who updated the table and when updated it

Question

Post reply

How to find who updated the table and when updated it

pavanv1899

SSC Enthusiast

Points: 173
More actions
February 19, 2021 at 4:39 am

#3847086

Hello,
Someone in my organization updated production table with update script. Can we find out this information anywhere. I have verified in sql server logs and event viewer but update event is not logged. Sql server version 2014
February 22, 2021 at 10:41 am

This was removed by the editor as SPAM
March 27, 2021 at 4:58 am

This was removed by the editor as SPAM
April 21, 2021 at 10:54 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

After the fact:

Restore database from backup (to a temporary database name Natch!), before the event, and compare the two

if you have transaction log backups then restore to POINT IN TIME (using the STOP AT) command to just before the event . That might need server attempts to figure out "When" the event was (unless you already know that)

That will enable you to figure out what data was changed, and exactly when, but not Who.

Before the fact:

We have Create / Update Date and User on everything single row of every single table in our database. (yeah, a couple of exceptions, but literally only one or tw0)

We also have an "Archive / Audit" table for every table, with a trigger that copies PREVIOUS record into Audit Table whenever it is changes (and adds a Timestamp and whether the record was Updated or Deleted)

At the time of the DML change the system has information about who is connected. Depends a bit how your APP works, it might connect with a single login for all users (and have its own method of logging in actual users), in which case the APP will have the User Details and can use that for the Audit data (in addition to, or instead of, the system data)

We also have a DDL trigger that stores any DDL change to the database

All that enables us to sort out "accidental delete / update of 10,000 customer records" without too much difficulty, and also detect / report on "Fraudulent / Malicious behaviour"

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

Daugherty wrote:

Try and read the Transaction Logs to see what happened.

Unless someone knows of an easy way I reckon that is "very hard" to do, in practice

Start trace in SQL Server profiler and checked events(TSQL-SQL:BatchCompleted,SQL:BatchStarting,SQL:StmtCompleted and SQL:StmtStarting)(Recommended).

That requires that Profiler was running at the time. Maybe it was, but if it was then I reckon the O/P would have known about it, and how to use that to solve the problem 🙂

FWIW we do not run Profiler continuously in order to be able to recover from a situation like this, we use Triggers/etc. to save Audit data to enable us to figure out, and reinstate, any accidental/fraudulent data changes

pavanv1899 SSC Enthusiast Points: 173 More actions · Answer 3

Thank you all for your replies.

I believe profiler can be used if we need to capture the details from the running processes. This incident came into the picture after two days.

There are no audit tables created on that table. Luckily, we have an extended event running for long-running queries and the information got captured over there.

Now, the next issue is that table is in replication and the update affected more than 10L rows and it starts created replication syncing issues from distributor to subscriber. I have the same no of rows 10 L in undistributed commands and it is showing high latency. Is there any way if we can apply all these transactions to the subscriber immediately without reinitializing the publication.