Solved

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

Posted on 2015-01-21
4
81 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
Comment Utility
Hi motioneye
As well as adding the DATABASE_OBJECT_CHANGE_GROUP
also please ADD
SCHEMA_OBJECT_CHANGE_GROUP
0
 

Author Comment

by:motioneye
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

763 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

13 Experts available now in Live!

Get 1:1 Help Now