-- Demostratin 2A (Using SQL Server Audit)
-- Step 1: Use the master database
USE master;
GO

-- Step 2: Create a SQL Server Audit and define tis target as
-- application log
Create Server Audit MarketDevLog
To Application_log
With (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
GO

-- Step 3: Change to the MarketDev database
USE MarketDev;
GO

-- Step 4: Create a database audit specification for SELECT
-- activity on the Marketing schema
CREATE DATABASE AUDIT SPECIFICATION MrketingSelectSpec
    FOR SERVER AUDIT MarketDevLog
    ADD (SELECT ON SCHEMA::Marketing BY public);
GO

-- Step 5: Query the sys.server_audits system view.
-- Scroll to the right and note the available columns.
-- particular, note the is_state_enabled column.

SELECT * FROM sys.server_audits;
GO

-- Step 6: Change to master database
USE master;
GO

-- Step 7: Enable the server audit
ALTER SERVER AUDIT MarketDevLog WITH (STATE=ON);
GO

-- Step 8: Change to MarketDev database
USE MarketDev;
GO

-- Step 9: Enable the MarketingSelectSpec audit specification
ALTER DATABASE AUDIT SPECIFICATION MarketingSelectSpec
    WITH (STATE = ON);

-- Step 10: Query the sys.server_audits and 
-- sys.database_audit_specifications and
-- sys.database_audit_specification_details system view
-- Note that the audit is now started and scroll to
-- see the details provided for the audit specification

SELECT * FROM sys.server_audits;
SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.database_audit_specification_details;
GO

-- Step 11: Geenerate an auditable event by querying a table
-- in the Marketing schema. Also execute a query
-- that should not be audited.

SELECT * FROM Marketing.PostalCode;
GO
SELECT * FROM DirectMarketing.City;
GO

-- Step 12: Check the contents of the Application log
-- (Do this by Start, Right-click My Computer, then
-- Expand Diagnostics, Event Viewer, and Windows Logs
-- click Application. Click on each of the MSSQLSERVER
-- the upper pane For each evetn, click on the detail
-- the lower pane and review the contents). the close
-- management windows.

-- Step 13: Change to the master database
USE master;
GO

-- Step 14: Disable the server audit
ALTER SERVER AUDIT MarketDevLog WITH (STATE=OFF);
GO

-- Step 15: Change to the MarketDev database
USE MarketDev;
GO

-- Step 16: Disable the MarketingSelectSpec audit specification
ALTER DATABASE AUDIT SPECIFICATION MarketingSelectSpec
    WITH (STATE = OFF);