• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

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
0
motioneye
Asked:
motioneye
  • 2
  • 2
2 Solutions
 
Barry CunneyCommented:
Hi motioneye
As well as adding the DATABASE_OBJECT_CHANGE_GROUP
also please ADD
SCHEMA_OBJECT_CHANGE_GROUP
0
 
motioneyeAuthor Commented:
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.
0
 
Barry CunneyCommented:
Hi Motioneye,
I have seen this discussed online and it appears that the DATABASE_OBJECT_CHANGE_GROUP is actually auditing the ALTER permission check on the SCHEMA as part of CREATE statement [and implied that it does not catch DROP or ALTER statements]

The SCHEMA_OBJECT_CHANGE_GROUP does appear to catch such DROP and ALTER statements.
0
 
motioneyeAuthor Commented:
Hi Barry Cunney
I have done some test with it and it appears that SQL server needs both audit specification ( DATABASE_OBJECT_CHANGE_GROUP & SCHEMA_OBJECT_CHANGE_GROUP )  in order to capture the alter and drop. One repercussion of  having both audit specification is this   caused some log are audited twice, to be specific  the "create".
Do you know any way that we can  exclude DATABASE_OBJECT_CHANGE_GROUP  with capture "Create"
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now