SQL – Determine which user deleted a SQL Server database

This is not too common, but usefull to know anyway. While searching for some answers to this question, I came to realize that the SQL default trace should be enough to find this information. If you are trying to find a reasonably recent information, yes it is.

Just open Windows Explorer, go to your server log folder (like “C:\Program Files\Microsoft SQL Server\MSSQL10_50.<INSTANCE_NAME>\MSSQL\Log“) and open up the last log*.trc file. It will open in SQL Profiler, there you should search for the string “– DB” on the ObjectType column for the events of type “Object:Deleted”. In the DatabaseName column you will find exactly that, and in the SessionLoginName you will have, well, the login of the user who deleted the database.

Pretty easy if your server is not too busy. If it is, you will need to set some form of DDL audit or something. I will investigate on this later.

Some links that can be usefull on this subject:

http://stackoverflow.com/questions/1043971/determine-which-user-deleted-a-sql-server-database
http://mitch-wheat.blogspot.com.br/2009/01/sql-servers-built-in-traces.html
http://www.sqlservercentral.com/Forums/Topic989304-391-2.aspx (this one has a nice script, but is broken by SQL2008 R2 due to an unexpected “_” character).

cya!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s