SQL Server Audit (script example)

This article is mainly script examples, more instructions to see Official documents: Auditing (Database Engine)

--The audit use master must be created in the master database; go--Create a Server Audit object--https://msdn.microsoft.com/zh-cn/library/cc280448 (v=sql.100). Aspxcreate server AUDIT [audit_tofile ]to file (--Target type: File/application_log (Application log)/security (security log) FILEPATH = N ' e:\ '--the path of the audit log, MAXSIZE = 100mb--audit files maximum size (MB, GB, TB, or UNLIMITED), max_rollover_files = 5--Maximum number of files (or UNLIMITED), Reserve_disk_space = on--Pre-allocated MAXSIZE size (maxsize< applicable when >unlimited. Default: OFF)) with (Queue_delay = 1000--forced pre-audit time, default 1000 (milliseconds), value 0 indicates synchronous delivery, On_failure = continue--cannot write to destination file: CONTINUE (default) | SHUTDOWN (requires permission)--, Audit_guid = <guid>--database Mirroring) go--Change the server audit object (as in create format)--https://msdn.microsoft.com/zh-cn/ Library/cc280563%28v=sql.100%29.aspx?f=255&mspperror=-2147217396alter SERVER AUDIT [audit_tofile]modify NAME = [ audit_tofile]--Change audit name Goalter SERVER Audit [Audit_tofile]with (state = ON)-Enable Audit Collection Records go--Delete Audit (Audit collection records must be disabled)--https://  msdn.microsoft.com/zh-cn/library/cc280899 (v=sql.100). Aspxalter SERVER AUDIT [Audit_tofile]with (state = OFF) GOIF EXISTS (SELECT * from Sys.servEr_audits WHERE name = N ' Audit_tofile ') DROP SERVER Audit [Audit_tofile]go 

--Create a server Audit specification object--https://msdn.microsoft.com/zh-cn/library/cc280767%28v=sql.100%29.aspx?f=255&mspperror=- 2147217396CREATE Server AUDIT Specification [audit_specification_server]for server AUDIT [audit_tofile]-- Apply audit name for this specification add (failed_login_group)-The name of the server-level auditable action Group (such as login failure auditing) with (state = ON)-Allows or disables auditing of records that collect this audit specification Go--sql Server Audit Action Group and Operation--https://msdn.microsoft.com/zh-cn/library/cc280663%28v=sql.100%29.aspx?f=255&mspperror=-2147217396-- Change the server Audit Specification object (the state of the audit specification must be set to OFF option)--https://msdn.microsoft.com/zh-cn/library/cc280682 (v=sql.100). Aspxalter Server AUDIT specification [Audit_specification_server]with (state = OFF) goalter Server AUDIT Specification [Audit_specificati On_server]for Server AUDIT [Audit_tofile]add (Successful_login_group), ADD (Logout_group), DROP (Login_change_password _group) with (state = ON) go/* The audit specification must be set to the OFF option in order to change the audit specification, otherwise an error occurs: Message 33229, Level 16, State 1, line 1th when the audit specification is disabled, changes to the audit specification must be completed. */--Delete the server Audit Specification object (the status of the audit specification must be set to OFF option)--https://msdn.microsoft.com/zh-cn/library/cc280603%28v=sql.100%29.aspx?f=255&mspperror=-2147217396alter SERVER AUDIT specification [Audit_specification_server] With (state = OFF) goif EXISTS (SELECT * from sys.server_audit_specifications WHERE name = N ' Audit_specification_server ' ) DROP SERVER AUDIT Specification [Audit_specification_server]go

--Create a Database Audit specification object (auditing is only created for the current database)--https://msdn.microsoft.com/zh-cn/library/cc280404%28v=sql.100%29.aspx?f=255& Mspperror=-2147217396use adventureworks2008r2; Gocreate DATABASE AUDIT Specification [audit_specification_database]for SERVER AUDIT [Audit_tofile] ADD (schema_object_ Change_group) with (state = on) gocreate DATABASE AUDIT specification [audit_specification_database]for SERVER AUDIT [Audi T_tofile] ADD (SELECT on dbo. Errorlog by [dbo]) and (state = ON) go--Change the Database Audit specification object (you must prevent auditing from collecting records for this audit specification)--https://msdn.microsoft.com/zh-cn/library/ Cc280645%28v=sql.100%29.aspx?f=255&mspperror=-2147217396alter DATABASE AUDIT Specification [Audit_ Specification_database]with (state = OFF) goalter Database AUDIT specification [audit_specification_database]for SERVER AUDIT [Audit_tofile]add (Update,insert,delete on dbo. Errorlog by [dbo]), DROP (SELECT on dbo. Errorlog by [dbo]) and (state = on) go--Delete the database Audit specification object (you must prevent auditing from collecting records for this audit specification)--https://msdn.microsoft.com/zh-cn/library/ Cc280479%28v=sql.100%29.aspx?F=255&mspperror=-2147217396alter DATABASE AUDIT Specification [audit_specification_database]with (state = OFF) GOif EXISTS (SELECT * from sys.database_audit_specifications WHERE name = N ' audit_specification_database ') DROP database A Udit Specification [Audit_specification_database]go

--Delete FROM dbo when the table errorlog of the database adventureworks2008r2 is deleted  . errorlog--audit log records are as follows

--Related View query select * FROM sys.server_audits--Audit Object select * from sys.dm_server_audit_status--the current status of the audit select * from Sys.server_ audit_specifications--Server Audit Specification Object SELECT * Audit action from sys.server_audit_specification_details--Server Audit Specification Object SELECT * FROM sys.database_audit_specifications--Database Audit Specification Object SELECT * from Sys.database_audit_specification_ Audit action for details--Database Audit Specification Object SELECT * FROM sys.dm_audit_actions--all available action groups select * from Sys.dm_audit_class_type_ map--field mappings in the audit log select * from sys.server_file_audits--audit file information select * from Sys.fn_get_audit_file (' e:\audit_tofile_*. Sqlaudit ', default,default);

SQL Server Audit (script example)