How to find out who dropped the database in sql server?
SUSER_SNAME([Transaction SID]) As UserName,
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.