Solved

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

Posted on 2015-01-21
4
97 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database Integrity 1 50
SQL Availablity Groups Shared Path 2 16
MS SQL SERVER and ADODB.commands 8 23
MS SQL + group by time 4 15
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

828 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