SQL Server Audit (Audit) configuration Method--Auditing of the server

Starting with SQL Server Enterprise , you can use SQL Server Audit to set up automatic auditing.

In the audit node, you can create one or more audit specifications that can be used for the server audit specification and the database audit specification. Auditing enables you to track events on a SQL Server database server.

Auditing can have the following categories of actions:

1. Server level: These actions include server operations, such as administrative changes and logon and logoff operations.

2. Database level: These operations include data manipulation language (DML) and data definition language (DDL) operations.

3. Audit level: These actions include actions during the audit process.

The server audit specification object belongs to auditing. You can create a server audit specification for each audit, because they are created within the scope of the SQL Server instance.

the Database Audit specification object is also part of SQL Server auditing. For each audit, you can create a database audit specification for each SQL Server database.

The database Audit specification collects database-level audit actions that are raised by the Extended events feature. You can add audit action groups or audit events to the database audit specification. An " audit event " is an atomic operation that can be audited by the SQL Server engine. The Audit Action Group is a predefined group of actions. They are all located in the SQL Server database scope. These actions are sent to the audit, and the audit logs them to the target.

The SQL Server auditing object collects a single server instance or database-level operation and action group for monitoring. This audit is at the SQL Server instance level. Each instance of SQL Server can have multiple audits.

Operation Instance steps:one, server-level auditing1.Create an audit

in a DB instance - Security - Real Core , the new audit

650) this.width=650; "title=" 1.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M02/58/4C/ Wkiom1st-pcgx2xaaaoj95jnxkq625.jpg "alt=" Wkiom1st-pcgx2xaaaoj95jnxkq625.jpg "/>

650) this.width=650; "title=" 2.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M00/58/49/ Wkiol1st-blaxb0xaaju4ugr14m937.jpg "alt=" Wkiol1st-blaxb0xaaju4ugr14m937.jpg "/>

Enter audit name Audit Login

in the audit target selection " file "

The folder path that was built before the file path was entered: c:\myadmin\audit_logs

650) this.width=650; "title=" 3.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M00/58/4C/ Wkiom1st-phdvgm-aanj7gecozk830.jpg "alt=" Wkiom1st-phdvgm-aanj7gecozk830.jpg "/>

Objects such as the newly created audit, audit specification, and so on, are disabled by default so they do not consume system resources. Need to be enabled for auditing, audit specifications, etc. to function

650) this.width=650; "title=" 4.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/58/49/ Wkiol1st-bowrs3taakvdat47r0110.jpg "alt=" Wkiol1st-bowrs3taakvdat47r0110.jpg "/>

2. Create a server-level audit Specification - monitor account creation, modification, and deletion

650) this.width=650; "title=" 5.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M00/58/4D/ Wkiom1st-txhx61iaaiwzepf0ks711.jpg "alt=" Wkiom1st-txhx61iaaiwzepf0ks711.jpg "/>

Server_principal_change_group is a server-level audit Action group that is raised when a server principal is created, altered, or deleted. This event is raised, for example, by executing the following stored procedure or statement, including: Spdefaultdb, Sp_defaultlanguage, sp_addlogin, Sp_droplogin, sp_grantlogin, sp_ Revokelogin, sp_denylogin and other stored procedures, or ALTER LOGIN, etc.

Specific Audit action type:

http://msdn.microsoft.com/zh-cn/library/cc280663 (v=sql.105). aspx

On the Create server Auditspecification interface

Input Name: Monitor Login

Click Audit--audit Login

650) this.width=650; "title=" 6.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/58/49/wKioL1St-5ewH_ Eeaar2odmj1p8201.jpg "alt=" Wkiol1st-5ewh_eeaar2odmj1p8201.jpg "/>

Once established, right-click to enable the server audit specification,

650) this.width=650; "title=" 7.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/58/4D/wKiom1St-tXg5_ Kraah2d8arhyo666.jpg "alt=" Wkiom1st-txg5_kraah2d8arhyo666.jpg "/>

3.Test Auditing features

Create a login account u01

650) this.width=650; "title=" 8.png "src=" Http://s3.51cto.com/wyfs02/M02/58/4D/wKiom1St_R6SaPT3AAEaWWECGbY376.jpg " alt= "Wkiom1st_r6sapt3aaeawwecgby376.jpg"/>

Then delete the account

4.View Audit Logs

650) this.width=650; "title=" 9.png "style=" Float:none; "src=" Http://s3.51cto.com/wyfs02/M02/58/49/wKioL1St_ Jbh8isxaaj3ylojqnk397.jpg "alt=" Wkiol1st_jbh8isxaaj3ylojqnk397.jpg "/>

650) this.width=650; "title=" 10.png "style=" Float:none; "src=" Http://s3.51cto.com/wyfs02/M02/58/4D/wKiom1St_ Xtwzdhxaapsfyuomgs931.jpg "alt=" Wkiom1st_xtwzdhxaapsfyuomgs931.jpg "/>

You can continue to see the information on the right, including the program generation that created the account.

This article is from "What is our favorite" blog, please be sure to keep this source http://pxizhi.blog.51cto.com/5283742/1600606

SQL Server Audit (Audit) configuration Method--Auditing of the server