How to find user who ran DROP or DELETE statements on your SQL Server Objects by Manvendra Singh

http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20131202

Problem

Someone has dropped a table from your database and you want to track who did it.  Or someone has deleted some data from a table, but no one will say who did.  In this tip, we will look at how you can use the transaction log to track down some of this information.

Solution

I have already discussed how to read the transaction log file in my last tip "How to read SQL Server Database Log file". Before reading this tip, I recommend that you read the previous tip to understand how the transaction log file logs all database activity.

Here we will use the same undocumented function "fn_dblog" to find any unauthorized or unapproved deletes or table drops. This tip will help you track or find any unethical or an unwanted user who has dropped a table or deleted data from a table. I strongly suggest testing any undocumented functions in a lab environment first.

One way to find such users is with the help of the default trace, because the default trace captures and tracks database activity performed on your instance, but if you have a busy system the trace files may roll over far too fast and you may not be able to catch some of the changes in your database.  But these changes are also tracked in the transaction log file of the database and we will use this to find the users in question.

.... follow above link