DDL search using SQL Server Default Trace

Need a quick way to search for recent changes to objects using the Default Trace? Then you my friend are in luck. Here is a stored procedure to search the Default Trace for recent object changes including the user, application, object, action, and date the change was made.

CREATE PROCEDURE DDLBusted
@DBName VARCHAR(255) = NULL,
@ObjectName VARCHAR(255) = NULL
AS

DECLARE @TraceFileName varchar (500)
SELECT @TraceFileName = PATH FROM sys .traces WHERE is_default = 1

SELECT   LoginName, HostName, ApplicationName, DatabaseName, ObjectName, s.name ObjectType, e.Name Action, StartTime, t.TextData
FROM   ::fn_trace_gettable(@TraceFileName, default) AS t
LEFT JOIN sys.trace_events AS e ON t.EventClass = e.trace_event_id  
LEFT JOIN master.dbo.spt_values s ON t.ObjectType = s.number
WHERE (@DBName IS NULL OR DatabaseName = @DBname)  
AND (@ObjectName IS NULL OR t.ObjectName = @ObjectName)
AND EventSubClass = 0
AND s.type = 'EOB'
ORDER   BY t.StartTime desc;

This can easily be adapted to store the data into an audit table, since entries in the Default Trace are not permanent. Feodor Georgiev has a tons more default trace info and better queries here: https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server—the-power-of-performance-and-security-auditing/

Advertisements