Microsoft SQL Server 2012 管理 (2): Auditing

標籤：style   blog   color   os   io   ar   for   art   div   

-- Demostratin 2A (Using SQL Server Audit)-- Step 1: Use the master databaseUSE master;GO-- Step 2: Create a SQL Server Audit and define tis target as-- application logCreate Server Audit MarketDevLogTo Application_logWith (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 schemaCREATE 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 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 specificationALTER 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 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 specificationALTER DATABASE AUDIT SPECIFICATION MarketingSelectSpec    WITH (STATE = OFF);

 

Microsoft SQL Server 2012 管理 (2): Auditing