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.

SUSER_SNAME([Transaction SID]) As UserName,
[Transaction Name],
[Begin Time],
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.  

No comments:

Post a Comment