January 7, 2014 at 1:01 pm
We're looking to Audit DDL statements against a specific database as a double-check that our schema objects aren't "drifting" prior to a new version deploy. Seems like a perfectly good use of SQL Audit.
I ran into an issue, that I can't seem to find a good answer to, but have a resolution for.
Same issue as reported here http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9d054247-3b4e-4794-92d0-04b94eddcd9f/sql-audit-alter-and-drop-table?forum=sqlsecurity
Our CREATE statements were being recorded, but not ALTER or DROP statements.
A strange nuance between using DATABASE_OBJECT_CHANGE_GROUP and SCHEMA_OBJECT_CHANGE_GROUP
described here http://technet.microsoft.com/en-us/library/cc280765.aspx
Database-Level Audit Action Groups
DATABASE_OBJECT_CHANGE_GROUP
This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas. Equivalent to the Audit Database Object Management Event Class.
Seems like that should do the trick, right?
Test it out...
USE [master]
GO
CREATE DATABASE TestAudit
GO
CREATE SERVER AUDIT [TestAudit]
TO FILE
(FILEPATH = N'D:\'
,MAXSIZE = 10 MB
,MAX_FILES = 1
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
USE [TestAudit]
GO
CREATE DATABASE AUDIT SPECIFICATION [Test Database Audit Specification]
FOR SERVER AUDIT [TestAudit]
ADD (DATABASE_OBJECT_CHANGE_GROUP)
GO
ALTER DATABASE AUDIT SPECIFICATION [Test Database Audit Specification]
WITH (STATE = ON)
GO
USE master
GO
ALTER SERVER AUDIT [TestAudit]
WITH (STATE = ON)
GO
USE TestAudit
GO
CREATE TABLE Test (Col1 Int);
ALTER TABLE Test ADD Col2 Int;
GO
CREATE PROC prTest AS SELECT * FROM Test;
GO
ALTER PROC prTest AS SELECT 1 FROM Test;
GO
DROP TABLE Test
DROP PROC prTest
SELECT statement AS 'USING DATABASE_OBJECT_CHANGE_GROUP' FROM sys.fn_get_audit_file ('d:\*',default,default)
WHERE statement <> ''
ORDER BY event_time;
GO
Only our CREATE statements are recorded. No Bueno.
But when we use SCHEMA_OBJECT_CHANGE_GROUP
This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema. Equivalent to the Audit Schema Object Management Event Class.
This event is raised on schema objects. Equivalent to the Audit Object Derived Permission Event Class. Also equivalent to the Audit Statement Permission Event Class.
It works as we need...
USE [TestAudit]
GO
ALTER DATABASE AUDIT SPECIFICATION [Test Database Audit Specification]
WITH (STATE = OFF)
GO
ALTER DATABASE AUDIT SPECIFICATION [Test Database Audit Specification]
DROP (DATABASE_OBJECT_CHANGE_GROUP)
GO
ALTER DATABASE AUDIT SPECIFICATION [Test Database Audit Specification]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
GO
ALTER DATABASE AUDIT SPECIFICATION [Test Database Audit Specification]
WITH (STATE = ON)
GO
USE TestAudit
GO
CREATE TABLE Test (Col1 Int);
ALTER TABLE Test ADD Col2 Int;
GO
CREATE PROC prTest AS SELECT * FROM Test;
GO
ALTER PROC prTest AS SELECT 1 FROM Test;
GO
DROP TABLE Test
DROP PROC prTest
SELECT statement AS 'USING SCHEMA_OBJECT_CHANGE_GROUP' FROM sys.fn_get_audit_file ('d:\*',default,default)
WHERE statement <> ''
ORDER BY event_time;
I haven't found any good justification/documentation. I'll chalk it up as ignorance on my part. Subtle difference between "statement is executed on database objects, such as schemas" and "operation is performed on a schema"
Anyway, good to go now, but maybe will save you some time!
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
April 3, 2014 at 2:21 am
Thanks for tip.
Meet same issue.
August 21, 2014 at 10:43 am
Could we get only drop to be captured (without create or alter)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply