DDL Auditing using the Default trace

• arr.nagaraj

  SSCertifiable

  Points: 6528

  More actions

  May 4, 2015 at 9:11 pm

  #307712

  Comments posted to this topic are about the item DDL Auditing using the Default trace

  Regards,
  Raj

  http://Strictlysql.blogspot.com

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 5, 2015 at 6:59 am

  #1795577

  A little deep for me. I'll pass this one on to the Admin.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  May 5, 2015 at 11:01 am

  #1795696

  I'd suggest changing the query to get the trace file name from:

  select convert(varchar(1000),path) as path from sys.traces where id = 1

  To

  select convert(varchar(1000),path) as path from sys.traces where is_default = 1

  if the server you run it on has C2 or common criteria enabled you aren't guaranteed that the default trace will get id = 1, but it will always have is_default = 1.

  Otherwise a good article. I'd like to see a follow-up that has some SSRS reports built on top of the data collected.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• arr.nagaraj

  SSCertifiable

  Points: 6528

  More actions

  May 5, 2015 at 9:39 pm

  #1795814

  Jack Corbett (5/5/2015)

  ---

  I'd suggest changing the query to get the trace file name from:

  select convert(varchar(1000),path) as path from sys.traces where id = 1

  To

  select convert(varchar(1000),path) as path from sys.traces where is_default = 1

  if the server you run it on has C2 or common criteria enabled you aren't guaranteed that the default trace will get id = 1, but it will always have is_default = 1.

  Otherwise a good article. I'd like to see a follow-up that has some SSRS reports built on top of the data collected.

  Thanks for the suggestion. Not sure I can edit the article as it has been published. Will attempt a follow up on SSRS reports as well

  Regards,
  Raj

  http://Strictlysql.blogspot.com