--Select MASTER Database
USE master
GO
--create SERVER Audit
CREATE SERVER AUDIT [FirstAudit]
TO FILE
(
FILEPATH=N'D:\AUDIT\'
,MAXSIZE=10 MB
,MAX_ROLLOVER_FILES=100
,RESERVE_DISK_SPACE=ON
)
WITH
(
QUEUE_DELAY=1000
,ON_FAILURE=SHUTDOWN
,AUDIT_GUID='2EB5EF64-1B15-4AFF-B248-6F39D423E2E7'
)
GO
-- From MSDN :
-- The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log.
-- Writing to the Security log is not available on Windows XP.
-- Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records.
ALTER SERVER AUDIT [FirstAudit]
WITH (STATE=ON)
GO
--if it is exists, droping it first
if exists(SELECT 1 FROM SYS.DATABASES WHERE name='AuditTest')
BEGIN
USE master
drop database AuditTest
END
CREATE DATABASE AuditTest
GO
--select newly created database
USE AuditTest
GO
--create two schema AUD1 and AUD2
CREATE SCHEMA Aud1
GO
CREATE SCHEMA Aud2
GO
--create two table
--one with each schema
if OBJECT_ID('Aud1.Table1') is not null drop table Aud1.Table1
CREATE TABLE Aud1.Table1 (id int)
GO
if OBJECT_ID('Aud2.Table1') is not null drop table Aud2.Table1
CREATE TABLE Aud2.Table1 (id int)
GO
--create database level AUDIT SPECIFICATION
--for our server audit created above
--which will keep watch on schema AUD1
--for DML statements but won't watch for AUD2 schema
CREATE DATABASE AUDIT SPECIFICATION [AuditTestSpec]
FOR SERVER AUDIT [FirstAudit]
ADD (SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Aud1] by [PUBLIC])
WITH (STATE=ON)
GO
--making some DML actions in both table
--of both schema
INSERT INTO Aud1.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
INSERT INTO Aud2.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
UPDATE Aud1.Table1 set id=6 where ID=5
GO
UPDATE Aud2.Table1 set id=6 where ID=5
GO
--looking at what we have received in our audit file.
--you will know that you got data for AUD1 schema only
SELECT * FROM sys.fn_get_audit_file('D:\Audit\*',DEFAULT,DEFAULT)
go
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: