Search code examples
sql-serversql-drop

Checking what 'DROP' queries were ran on SQL Server

Some one keeps dropping tables on one of our database's as soon as I gain access the server. I don't know who this some one is. I have nearly lost my job once because of this person.

So I was wondering is there a way I can check which user ran a query for DROP TABLE my_table so that I can prove to my boss I am innocent?

Solution

  • On SQL Server 2005 or newer, you could also investigate DDL triggers which would even allow you to prohibit certain DROP TABLE statements....

    CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE
AS 
   PRINT 'You must disable Trigger "safety" to drop tables!' 
   ROLLBACK
;

    This would basically just prevent anyone from dropping a table