SQL Server Performance

Is there anyway to find out when a table was dropped, and by what user?

Discussion in 'SQL Server 2005 General DBA Questions' started by null, Dec 30, 2008.

  1. null New Member

    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.
  2. ndinakar Member

    dont think so...
  3. gbd77rc New Member

    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...
  4. FrankKalis Moderator

    [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.
  5. gbd77rc New Member

    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...
  6. null New Member

    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.

Share This Page