Who created these tables?!

  • Good morning-

    We do not have DDL auditing turned on currently; that said, is there any way I can find out the user that created a table? I found a bunch of AdHoc tables in the Produciton database today, and I'm curious as to where they came from....

    Thanks much,

    Jason

  • Jason Marshall (3/13/2008)


    Good morning-

    We do not have DDL auditing turned on currently; that said, is there any way I can find out the user that created a table? I found a bunch of AdHoc tables in the Produciton database today, and I'm curious as to where they came from....

    Thanks much,

    Jason

    The property function of each table will tell you WHEN it was created, which is a start - have you viewed the transaction log as I'd expect it to be logged there?

  • If the default trace is enabled you should be able to get that information.

    For example:

    -- Check if default trace is enabled; id=1.

    SELECT * FROM sys.traces

    -- Change to suspect database so object_name will resolve

    USE myDatabase

    SELECT t1.starttime, t1.eventclass, t1.databaseid, t1.ntusername, t1.objectid, object_name(t1.objectid), t2.name

    FROM fn_trace_gettable(<path from sys.traces>,default) t1

    JOIN sys.trace_events t2 ON t2.trace_event_id = t1.eventclass

    ORDER BY t1.starttime DESC

  • Or maybe even more easy, look in the SSMS Schema Change History report.

    http://blogs.msdn.com/buckwoody/archive/2008/02/25/sql-server-management-studio-standard-reports-schema-changes-history-databasename.aspx

    [font="Verdana"]Markus Bohse[/font]

  • Excellent, thanks all

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply