Odds are that if you are reading this; someone, somewhere, somehow… has changed your data. Odds are also, you may be out of luck figuring out what changed and who did it. The good news is that the lesson has been learned and you can take action now to prevent this in the future.
SQL Server by default does not automatically track who does what to your tables. It does track the made to your database, but it won’t tell you “who done it?”. You can however use the transaction logs with extreme painstaking patience to reveal what changes were made.
There are features such as Change Data Tracking and Change Data Capture, this can potentially give you some better insight to the changes as they occur. Unfortunately, when it looks at who made the changes, it is only going to post the principal account running CDC/CDT and it will not reveal the culprit.
There is real-time tracing with SQL Profiler, but this creates extreme load on the server and it does not reveal the past. You can utilize SQL’s Dynamic Management Views and Extended Events to capture SQL in real time along with wait types, execution plans, and much more in depth information that doesn’t put as much load on the system. Sp_whoisactive does exactly this! Thank you Adam! So I run this every few minutes to capture data and do analysis on what my long running queries are and what my wait types look like, but more importantly on topic, it captures who ran what query and when. I run other scripts that I will eventually load to github and post for everyone, as well as a more in detailed view on how I performance tune my T-SQL.
The problem with real-time analysis, is that you have to catch it in real-time. There’s the chance the SQL ran too fast or sp_whoisactive was not running at the same time. This leads us to the only real solution, triggers.
I won’t dive into HOW to do this, because that is reinventing the wheel, but I will leave this resource that covers creating an audit trail to figure out what changed and by whom and when.