asked on

sql2008 R2 - Database audit specification ( no logged on drop and alter objects commands )

Hi,,

Hi,
I have following database audit specification which is now running in sql2008 R2, we do some test with the table creation and we seen it capture the log, but when we drop the table, or modify the table, this activities did not get logged.

someone where know about this and a way to fix the audit to work better ?

USE [DBProton]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification_DBProton]
FOR SERVER AUDIT [DBProton_Database_Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

ASKER CERTIFIED SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

motioneye

ASKER

Hello,
I'm reading from Mcsft link https://msdn.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

and it says below for DATABASE_OBJECT_CHANGE_GROUP descriptions, so I assume this audit specification will works to capture those drop / alter.

Do you think any other thoughts why I must include SCHEMA_OBJECT_CHANGE_GROUP ?

This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas. This event is raised whenever any database object is created, altered or dropped.

SOLUTION