Thursday, 17 January 2019

Who changed DB Objects in MSSQL Server?

If you want to trace that who changed the db objects you can find it by using following Sql Server script. Bellow script is given to trace the changes of a procedure myProcName in last 7 days in database myDbName.

--From trace file

DECLARE @filename VARCHAR(255);
SELECT @filename = SUBSTRING(path, 0, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

--From DB
SELECT gt.HostName,
       gt.EventClass, AS EventName,
FROM [fn_trace_gettable](@filename, DEFAULT) gt
    JOIN sys.trace_events te
        ON gt.EventClass = te.trace_event_id
WHERE EventClass IN ( 164 )
      --AND gt.EventSubClass = 2
      AND gt.ObjectName LIKE ('%myProcName%')
      AND DatabaseName = 'myDbName'