DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 114--------------------------------------------------
       SQL Audit WHERE clause
       By: srinivasma_exceldbp Date: December 11, 2014, 5:05 am
       ---------------------------------------------------------
       -- WHERE clause in server audit
       /*
       The table named DataSchema.SensitiveData will contain
       confidential data and
       access to the table must be recorded in the audit. The table
       named DataSchema.GeneralData
       does not contain confidential data. The database audit
       specification audits access to all objects in the DataSchema
       schema.
       */
       CREATE DATABASE TestDB;
       GO
       USE TestDB;
       GO
       CREATE SCHEMA DataSchema;
       GO
       CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY,
       DataField varchar(50) NOT NULL);
       GO
       CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY,
       DataField varchar(50) NOT NULL);
       GO
       -- Create the server audit in the master database
       USE master;
       GO
       CREATE SERVER AUDIT AuditDataAccess
       TO FILE ( FILEPATH ='C:\temp\' )
       WHERE object_name = 'SensitiveData' ;
       GO
       ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);
       GO
       -- Create the database audit specification in the TestDB
       database
       USE TestDB;
       GO
       CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]
       FOR SERVER AUDIT [AuditDataAccess]
       ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
       WITH (STATE = ON);
       GO
       -- Trigger the audit event by selecting from tables
       SELECT ID, DataField FROM DataSchema.GeneralData;
       SELECT ID, DataField FROM DataSchema.SensitiveData;
       GO
       -- Check the audit for the filtered content
       SELECT * FROM
       fn_get_audit_file('C:\temp\AuditDataAccess_*.sqlaudit',default,d
       efault);
       GO
       *****************************************************