Microsoft SQL Server 2012 管理 (2): Auditing

            --
             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 databaseUSE MarketDev;
GO-- Step 4: Create a database audit specification for SELECT
-- activity on the Marketing schemaCREATEDATABASE AUDIT SPECIFICATION MrketingSelectSpec
    FOR SERVER AUDIT MarketDevLog
    ADD (SELECTONSCHEMA::Marketing BYpublic);
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 databaseUSE master;
GO-- Step 7: Enable the server auditALTER SERVER AUDIT MarketDevLog WITH (STATE=ON);
GO-- Step 8: Change to MarketDev databaseUSE MarketDev;
GO-- Step 9: Enable the MarketingSelectSpec audit specificationALTERDATABASE 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 specificationSELECT*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;
GOSELECT*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 databaseUSE master;
GO-- Step 14: Disable the server auditALTER SERVER AUDIT MarketDevLog WITH (STATE=OFF);
GO-- Step 15: Change to the MarketDev databaseUSE MarketDev;
GO-- Step 16: Disable the MarketingSelectSpec audit specificationALTERDATABASE AUDIT SPECIFICATION MarketingSelectSpec
    WITH (STATE =OFF);