Earlier today while writing a blog on how to query the SQL default trace for a specific information, I was reminded of a queer request from my project manager, which had happened quite a long time back.
We have some small number of non-critical internal SQL instances hosting some internal applications. Someone had over a period of time made some structural changes to the tables and no change records were maintained.
One fine day, my project manager comes running to me and says “can you provide me with information on who has modified the structure of tables x, y, z in the “ABC” database on the “DF” SQL instance in the last 3 months. “Also please can I have the data in the next 30 minutes”?
The SQL instance in question was a 32 bit 2005 standalone.
When I had a look around on the SQL instance, I found no DDL auditing was ever configured neither on database level nor on SQL instance level.
Note: DDL auditing on databases can be easily configured by creating DDL triggers for ALTER, DROP & CREATE statements.
Now I needed to give the info to my project manager, which normally should have been available from DDL audit records, within a duration of 30 minutes. Again the default trace came to my rescue.
Thank God, no one in the internal applications team had disabled the default trace.
The below query lists out who all had created or altered or deleted objects in the “ABC” database over a period of last 3 months.
IF (SELECt convert(int,value_in_use) FROM sys.configurations WHERE name = 'default trace enabled') = 1 BEGIN DECLARE @curr_tracefilename varchar(500) ; DECLARE @base_tracefilename varchar(500) ; DECLARE @indx int ; SELECT @curr_tracefilename = path from sys.traces where is_default = 1 ; SET @curr_tracefilename = reverse(@curr_tracefilename); SELECT @indx = patindex('%\%', @curr_tracefilename) ; SET @curr_tracefilename = reverse(@curr_tracefilename) ; SET @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ; SELECT te.Name as DDL_Type, DB_NAme(DatabaseID) as DBName, HostName, ApplicationName, LoginName,ObjectType,ObjectName,ObjectID,StartTime,EventSubClass FROM ::fn_trace_gettable( @base_tracefilename, default ) t INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE DB_NAme(DatabaseID) = 'ABC' AND StartTime >= '2011-01-25 17:00:00.000' AND StartTime <= '2011-04-27 19:00:00.000' AND te.Name IN ('Object:Created', 'Object:Altered', 'Object:Deleted') END