Header_Footer

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.

 

Regards

Vasudev Menon

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

SQL Server: MSDB in FULL RECOVERY Model in SQL Server
SQL Server: How to query from default trace on DATA & LOG file autogrow events