Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-21
4
Medium Priority
?
117 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 2000 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

926 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