Monday, April 21, 2014

Identify user who modified the table recently

DECLARE @Filename VARCHAR(500)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;
--SELECT @FileName AS [Log Trace File Name]
SELECT gt.HostName
    , gt.ApplicationName
    , gt.DatabaseName
    , gt.LoginName
    , gt.SPID
    , gt.EventClass
    , te.Name AS EventName
    , gt.TEXTData
    , gt.StartTime
    , gt.EndTime
    , gt.ObjectName 
FROM [fn_trace_gettable](@filename, DEFAULT) gt
    JOIN sys.trace_events te
        ON gt.EventClass = te.trace_event_id
WHERE EventClass = 164
ORDER BY StartTime DESC;

No comments:

Post a Comment