I'm trying to find out when a table was dropped, and by what user ... though I couldn't find out any info on how to do this. DDL triggers weren't in place to capture the event ... is there another way? Thanks.
Hi have you checked the default trace log? By default it is on, and is stored in log folder under the instance. You use this statement to get the trace file name and path. SELECT * FROM ::fn_trace_getinfo(0) You use this statement to get the list of events that have happened since last rollover. You should be able to view other log files if they still exist. SELECT * FROM ::fn_trace_gettable ('<trace file location>log.trc', default) I hope this helps. Regards Richard...
[quote user="null"] I'm trying to find out when a table was dropped, and by what user ... though I couldn't find out any info on how to do this. DDL triggers weren't in place to capture the event ... is there another way? Thanks. [/quote] I don't think you can do so once the fact has occured. On the other hand you should really think about your permissioning model. No "ordinary" user should have such escalated rights in a production database. No one except for the DBAs. If that's the case, your circle of suspects should be fairly small.
Hi All, As long as the default trace is running you should be able to see when the object (table) was dropped (event class id 47) and by which connection/spid that did it. I would limit the result set from ::fn_trace_gettable with the following where clause. WHERE EventClass = 47 AND DatabaseID = DB_ID() --Or the actual id of the database you are interested in. But as already been said only the database owner, system admin or user with specific permission can drop tables (thats why you should never have a hardcoded connection string with sa as the user []). Regards Richard...
Very nice, this info definitely helped. As per the root of the problem, yes, this was indeed due to improperly manged permissions for a specific user. That's been solved, and luckily no major damage came from this. Thanks for your help.