Microsoft SQL Server 2012 Management (2): Auditing

--demostratin 2A (Using sql Server Audit)--Step 1:use the master database UseMaster;GO--Step 2:create A SQL Server Audit and define tis target as--Application LogCreateServer Audit Marketdevlog toApplication_log with(Queue_delay=  +, On_failure= CONTINUE)GO--Step 3:change to the Marketdev database UseMarketdev;GO--Step 4:create A Database audit specification for SELECT--activity on the Marketing schemaCREATE DATABASEAUDIT Specification Mrketingselectspec forSERVER AUDIT MarketdevlogADD(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 *  fromsys.server_audits;GO--Step 6:change to master database UseMaster;GO--Step 7:enable the server auditALTERSERVER AUDIT Marketdevlog with(state= on);GO--Step 8:change to Marketdev database UseMarketdev;GO--Step 9:enable The MARKETINGSELECTSPEC Audit specificationALTER DATABASEAUDIT 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 are now started and scroll to--See the details provided for the audit specificationSELECT *  fromsys.server_audits;SELECT *  fromsys.database_audit_specifications;SELECT *  fromsys.database_audit_specification_details;GO--Step 11:geenerate An auditable event by querying a table--In the Marketing schema. Also Execute a query--That's should not being audited.SELECT *  fromMarketing.postalcode;GOSELECT *  fromdirectmarketing.city;GO--Step 12:check The contents of the Application log--(Do the by Start, right-click My Computer and 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 UseMaster;GO--Step 14:disable the server auditALTERSERVER AUDIT Marketdevlog with(state=OFF);GO--Step 15:change to the Marketdev database UseMarketdev;GO--Step 16:disable The MARKETINGSELECTSPEC Audit specificationALTER DATABASEAUDIT Specification Marketingselectspec with(state= OFF);

Microsoft SQL Server 2012 Management (2): Auditing