SQL Server DB auditing

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  January 2, 2019 at 2:44 pm

  #378924

  Hello,

  I am trying to make change in the existing SQL auditing for all user databases.
  How can I loop through each DB to run this script?
  

  Begin

  EXEC sp_MSforeachdb 'USE ?

  IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N''DatabaseAuditSpecification'')

  BEGIN

  ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification WITH (STATE = OFF)

  DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification

  END

  CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]

  FOR SERVER AUDIT [Audit]

  ADD (DATABASE_CHANGE_GROUP),

  ADD (EXECUTE ON DATABASE::DBName BY [public])

  WITH (STATE = ON)'

  END

  Thank you.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 2, 2019 at 4:47 pm

  #2017812

  Use another question mark?

  As a bit of an important sidebar, there's no way on this good Green Earth that I'd ever grant EXECUTE for an entire database to the PUBLIC role regardless of intended use.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• January 4, 2019 at 10:02 pm

  This was removed by the editor as SPAM