SQL Server Performance Forum – Threads Archive

How to find out who deleted the table

I’d like to find out who deleted a table in a database. One of the tables in my database got deleted, and I have to restore it from the backup. In the meantime, manager wants to know who deleted the table. Any input will be appreciated.

Don’t know if this sort of action gets logged in the transaction log … in that case you could use one of the 3rd party tools to ‘read’ the log.

Blame it on one of the developers you don’t like (just kidding <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Like Adriaan said, you can use a third party tool like Log Explorer to read the log, but I guess only to get the time of this delete. Then you may need to find out the users logged in (it will be easy if you have audit enabled) during this time and narrow it down to few users.

AFAIK it’s impossible to find this out after the event has occured. Like Adriaan said, third-party tools can handle this, however they would have to be installed before not after. So, you’re pretty much out of luck, I guess. ———————–
–Frank
http://www.insidesql.de
———————–

Yes it will be logged to the transaction log and ensure the log is not set to SIMPLE RECOVERY model or ‘trunc.log at chkpt.’. If so there will no chance to know the fact if at all you’ve deployed the third party tool to find the culprit. In the meantime if you want to perform BIG BROTHER task then use PROFILER which is best tool to monitor. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Yes it will be logged to the transaction log and ensure the log is not set to SIMPLE RECOVERY model or ‘trunc.log at chkpt.’. If so there will no chance to know the fact if at all you’ve deployed the third party tool to find the culprit. <hr height="1" noshade id="quote"></blockquote id="quote"> When that developer has used a DROP TABLE statement, only the fact will be recorded in the logs. Same as TRUNCATE. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> In the meantime if you want to perform BIG BROTHER task then use PROFILER which is best tool to monitor. <hr height="1" noshade id="quote"></blockquote id="quote"> But useless when started after the DROP [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] But it brings up another question: Since this was obviously NOT a test system, why do your developer have such privileges on production systems. You urgently need to revised your security and permission concept, methinks. ———————– –Frank http://www.insidesql.de</a> ———————–

quote:But useless when started after the DROP

.. well atleast for future reference you can deploy it [8D] Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya .. well atleast for future reference you can deploy it [8D] <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] …along with a massive slap on the hand of that developer (if you can find out, otherwise slap all [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]) and a modified permission concept. ———————– –Frank http://www.insidesql.de</a> ———————–

]]>