Best and simple way to know,who created/dropped/altered the database?

Category: sql server search

Question

Irfankhan1995 on Sun, 17 Mar 2019 04:42:48


Hello

What is simple and easiest way to know,who created/dropped/altered the database?

Thanks in advance.

Replies

Visakh16 on Sun, 17 Mar 2019 05:24:52


If you have audit log being captured somewhere you can get this information from it

Other methods are to get information from standard reports in SSMS or from default trace info 

like

https://blogs.msdn.microsoft.com/sqlserverfaq/2015/05/17/how-to-find-which-user-deleted-the-user-database-in-sql-server/

Yet another way is to query the log using fn_dblog function and get this info

see examples here

https://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/

https://raresql.com/2015/05/31/sql-server-how-to-determine-who-dropped-database-at-what-time/

If transaction has been cleared from active portion of the log you would need to use fb_dump_dblog instead as explained here

https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

Times Decides on Sun, 17 Mar 2019 12:10:25


https://blog.sqlauthority.com/2015/09/12/sql-server-who-dropped-table-or-database/

scott_morris-ga on Sun, 17 Mar 2019 12:34:30


Give those permissions to one and only one person.