3

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?

Kara
  • 6,115
  • 16
  • 50
  • 57
Jacques Koekemoer
  • 1,378
  • 5
  • 25
  • 50
  • 1
    @Barry I know it's an odd question, but I don't think deleting all the background information helps. – Tim Rogers May 15 '12 at 14:49
  • @Tim - I don't think all the background info adds any value to what is actually an interesting question. It's generally the sort of blurb that encourages a question to be closed. – Barry Kaye May 15 '12 at 14:52

1 Answers1

2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Combine this with an insert into a log table and you could both protect the table from being dropped and gather evidence of who/when it was attempted – bendataclear May 15 '12 at 15:12