Find out who has changed what | SansSQL

Sunday, May 24, 2009

Find out who has changed what

There are many cases where the objects in a database gets changed without any information to the admins. This may be accidenatal or ----- :) .
So if your server instance has the default trance enabled, then you can find out who has changed what in you databases.

1. This Query gives the trace flie path.
    SELECT * FROM ::fn_trace_getinfo(0)
2. Execute the Below Query to get the data from trace file. The filters can also be applied to the below query to get the exact data
    SELECT * FROM ::fn_trace_gettable (':\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_59.trc', default)
3. Map the trace table to find what type of event has happened.
    SELECT, T.*

    FROM dbo.temp T -- table that contains the trace results
    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
4. To get the list of possible events
    SELECT * FROM sys.trace_events where name like '%alter%' ORDER BY trace_event_id

1 comment:

Anonymous said...

Dear Sandy,
This information helped someone A--. thanks. Keep up the good work... ;-)

Post a Comment