Solved

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

Posted on 2015-01-21
4
86 Views
Last Modified: 2015-02-14
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
Comment
Question by:motioneye
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 40561649
Hi motioneye
As well as adding the DATABASE_OBJECT_CHANGE_GROUP
also please ADD
SCHEMA_OBJECT_CHANGE_GROUP
0
 

Author Comment

by:motioneye
ID: 40561754
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
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 500 total points
ID: 40561811
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
 

Author Comment

by:motioneye
ID: 40561933
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now