Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 29 July 2016

How to find out who dropped the database in sql server?

Well, it is such a vast topic, and there are multiple ways to find out however What I have used is undocumented process which helped me to retrieve the information really quickly. I don't mind using this statement as it really selects the information.

SELECT
Operation,
SUSER_SNAME([Transaction SID]) As UserName,
[Transaction Name],
[Begin Time],
[SPID],
Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'dbdestroy'

First of all fn_dblog undocumented function which reads the SLQ Server Log, if you select all the columns, it has some great information however, just to illustrate this topic, I am looking for a user who has dropped the database.

If you notice, I have Transaction Name column filter with value "dbdestroy", you can explore the fn_dblog and see what exactly are the operation specified in transaction name column.

This statement can also be used in finding out who dropped the object in a particular database as well.

SUSER_SNAME function will convert Transaction SID which looks like (0x01050000000000051500000061AB3961A243C225349FF8F250A50000) into the readable username which was holding this session when drop database operation took place.