3

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?

  • 2
    Do note that coming down hard on whoever did it is unlikely to prevent future problems and is likely to cause workplace issues. This was more than likely a mistake (even if it was a major one). You would profit much more from trying to rework your standards to avoid these kinds of mistakes. (Also note that "review everything" is probably not a good way to accomplish that; whatever you come up with should be lightweight in the normal, safe cases and should only add overhead when an actual risk presents itself.) – jpmc26 Jan 17 '17 at 18:57
7

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.

| improve this answer | |
  • Thanks, looks like I'm not able to catch what I'm looking for this time. My thought was to apply CDC and have an answer in the feature, but I was wondering if it is possible to see if the user activity is being stored somewhere in SQL. – reshad Jan 17 '17 at 16:37
  • 4
    @reshad The Transaction Logs do not contain the principal that made the modification. Meaning, without a Trigger-based audit mechanism in place before a change is made, there is no way to go back and find out who did it. – Solomon Rutzky Jan 17 '17 at 17:01
  • 1
    Keep in mind this assumes you have 1 database user (possibly via Active Directory) per end user. If the change was made through a web app, then this won't help at all. The suspicion that a generic user modified the data would suggest that something like this may be the case. – jpmc26 Jan 17 '17 at 18:54
0

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!

| improve this answer | |
  • @ Rahim, I think adding your recommended script would capture the user and the original login would be helpful. Thanks – reshad Jan 18 '17 at 16:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.