SQL Server Audit (Audit)--Create server-level auditing

SQL Server Audit (Audit )-- to create a server-level audit

Task 1 : Create an audit

Step 1 : open SSMs, log on to the specified instance, expand the "Security", "audits" node.

Step 2 : on the audits node, right-select the "New Audit ..." option.

Step 3 : in the "Create Audit" window, enter the following parameters.

L Enter Audit-login-create/alter/drop in "Audit name".

L Select "File" in "Audit destination"

L Enter D:\MSSQL\DATA\Audit_logs in "File path".

Step 4 : Click OK to complete the setup.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M01/57/84/wKiom1ScuXrBmMQRAAP-Q746DLc304.jpg "border=" 0 "height=" 674 "/>

Step 5 : Right-click on the audit "Audit-login-create/alter/drop" you just created and select the "Enable Audit" option.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M01/57/82/wKioL1ScuinjOzySAAC8foYJuLU918.jpg "border=" 0 "height=" 268 "/>

Note: The newly created objects such as "audits", "Server Audit Specifications" are disabled by default, so system resources are not consumed. They need to be enabled for them to function.

Task 2 : Create a server-level audit specification – monitor account creation, modification, and deletion.

Step 1 : in the Object Explorer window, expand the "Security", "Server Audit Specifications" node.

Step 2 : Right-click on "Server Audit Specifications" and select "Create Server Audit Specification" option.

Step 3 : in the "Create Server Audit Specification" window, enter the following parameters.

L Enter Serverauditspecification-login-create/alter/drop in "Name".

L Select the previously created Audit object "Audit-login-create/alter/drop" in "Audit".

L in the "Actions" area, under "Audit Action Type", select "Server_principal_change_group" in the first line.

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M02/57/84/wKiom1ScuX_h3f52AAPZEx_m_Dg366.jpg "border=" 0 "height=" 674 "/>

"Server_principal_change_group" is a server-level audit Action group that is raised when a server principal is created, altered, or deleted. For example, this event is raised when the following stored procedure or statement is executed, including sp_defaultdb, Sp_defaultlanguage, sp_addlogin, Sp_droplogin, sp_grantlogin, sp_ Stored procedures such as Revokelogin, sp_denylogin, or ALTER LOGIN statements.

Step 4 : Click "OK" to complete the setup.

Step 5 : Right-click on the server-level audit specification "Serverauditspecification-login-create/alter/drop" you just created and select "Enable Server Audit Specification ".

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M02/57/84/wKiom1ScuX_Q2k3LAAC_zzrW09I933.jpg "border=" 0 "height="/>

Task 3 : Test auditing features – Create, modify, and delete login accounts.

Step 1 : Open SSMs, expand "Security", "Logins" node, create login account, for example: Ultrasql.

Step 2 : Change the "Default database" of this login account to AdventureWorks2012.

Step 3 : Delete this login account again.

Task 4 : View the audit log.

Step 1 : Expand "Security", "audits" node.

Step 2 : on the "Audit-login-create/alter/drop" object you just created, right-select "View Audit Logs".

Step 3 : in the "Select Logs" area in the upper left corner of the "Log File Viewer" window, verify that "Audit Collection" and "Audit-login-create/alter/drop" are checked.

Step 4 : In the right pane, under the Action ID box in the Log file summary area, select CREATE.

Step 5 : In the lower pane "Statement" area, you can see the program code that created the account, the password is specifically "* * * *" protection.

650) this.width=650; "title=" clip_image005 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M00/57/84/wKiom1ScuYGzUbnyAAbPaSCYBdk758.jpg "border=" 0 "height=" 644 "/>

Under the Action ID column, you can see the type of each event that is logged, such as Create, change, drop, and so on, to view the audit information on a per-pen basis.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1596117

SQL Server Audit (Audit)--Create server-level auditing