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.ApplicationName,
       gt.NTUserName,
       gt.NTDomainName,
       gt.LoginName,
       gt.SPID,
       gt.EventClass,
       te.name AS EventName,
       gt.EventSubClass,
       gt.TextData,
       gt.StartTime,
       gt.EndTime,
       gt.ObjectName,
       gt.DatabaseName,
       gt.FileName,
       gt.IsSystem
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'
      AND StartTime >= DATEADD(DAY, -7, CONVERT(DATE, GETDATE()))
ORDER BY StartTime DESC;