How to find who changed a table value in SQL Server 2014?

Question

We have a few tables on a database called mydb which is being used by applications to store, or retrieve records. Somehow, someone changed a value on the test table and that caused a serious issue.

We are receiving constant data from different sources, when the data is being updated on the table, a trigger fires and checks for the specific value, if the values is 360 then it will update the rows, else it will do nothing.

Now I have two questions:

1. I want to know who changed that value 4 days ago?
2. I dont see any successful login (user login) in the SQL server error log. I assume that someone has used a generic user account named user. Now who used that account to login?

The value on the table is supposed to be 350, but it was changed to 800.

Any tips?

Answer 1

SQL Server does not keep track of who does what without you setting it up, now is a great time to set this up because you'll catch it the next time. You can try and read the Transaction Logs to see what happened (minus the principal account used to make the change), but otherwise you'll want something set up in the future.

The best approach to see who changed what and when is putting triggers on your tables when they change, you can audit the information by inserting what changed into the audit table. I add fields such as getdate and suser so I can see who modified and when it was modified.

An even lighter version is logging sp_whoisactive or other monitoring tools using DMV's. This will not catch everything.

CT / CDC is another option, however they will not reveal who made the change. You can make an alteration to the triggers so it would also use suser, but it will only reveal the account running CT / CDC.

If upgrading to 2016 or higher is a viable option, I would recommend looking at temporal tables combined with SQL Auditing. Temporal tables would give you the in-depth analysis of what changed and the SQL Audit would give you insight into who made said changes. Here's an excellent answer by Nic on stack exchange to set up the SQL Audit.

Answer 2

I would suggest adding an audit table and triggers for update on that table.

Considering that it is not being updated that often. Use SUSER_SNAME() and ORIGINAL_LOGIN() to capture your perpetrator. Good luck!