Find out who dropped a table in a db that's in simple mode.
-
Is there a way to Find out who dropped a table in a db that's in simple mode? My guess would be the person that first noticed?
Can't find anything in the event viewer or sql log.
versions:
SQL 2005 Enterprise
Windows Server 2003 R2
-
Drop table only allowed by db_owner and ddl_admin permission at the database and sysadmin at the server level. I am not sure if you can filter out who are the potential curlprit ids by permission.
-
There won't be unless you have certain traces or auditing types on, or server/database event triggers.
You won't be able to track this back, not 'officially'.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
if not too much time has past, the default trace, which logs DDL events like CREATE ALTER DROP, might still have it:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (6/1/2011)
if not too much time has past, the default trace, which logs DDL events like CREATE ALTER DROP, might still have it:Good call, Lowell, I forgot about the rolling window default. Don't most folks turn that off these days though?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
dunno about most folks; since it's on by default, and it's a low impact trace, i'm under the impression most people don't actively go and disable it;
I personally think there should be three default traces..one for DMl, one for logins, and the third for the DDL trace that we already know and love.
I try to always add some to my servers, but my shop is more development,a nd not high speed super active processing or anything... works for me, would probably be ok for some, but not others.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Craig Farrell (6/1/2011)
Lowell (6/1/2011)
if not too much time has past, the default trace, which logs DDL events like CREATE ALTER DROP, might still have it:Good call, Lowell, I forgot about the rolling window default. Don't most folks turn that off these days though?
I very much doubt it.
From SQL2008R2 there is the auditing option, and there can be a lot of overlap there with the default trace.
---------------------------------------------------------------------
-
Lowell (6/1/2011)
if not too much time has past, the default trace, which logs DDL events like CREATE ALTER DROP, might still have it:Thanks Lowell, that was the ticket! FYI - the developer that asked about the missing table was the culprit.
-
SkyBox (6/1/2011)
...Thanks Lowell, that was the ticket! FYI - the developer that asked about the missing table was the culprit.
There are some bets where one can almost always win. This is a good example... ("I bet the guy asking where the table/data/money is is actually the one that dropped/deleted/'borrowed' it before.")
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply