SQL Server audit (script example) and SQL Server example

SQL Server audit (script example) and SQL Server example

This article is mainly a script example. For more information, see the official document: Review (database engine)

-- Audit USE master must be created in the master database; GO -- create server Audit Object -- https://msdn.microsoft.com/zh-cn/library/cc280448 (v = SQL .100 ). aspxCREATE server audit [Audit_ToFile] to file (-- target type: FILE (FILE)/APPLICATION_LOG (Application Log)/SECURITY (SECURITY Log) FILEPATH = n'e: \ '-- Audit Log Path, MAXSIZE = 100 MB -- maximum audit file size (MB, GB, TB, or UNLIMITED), max_rolover_files = 5 -- maximum number of files (or UNLIMITED ), RESERVE_DISK_SPACE = ON -- pre-allocated MAXSIZE (MAXSIZE <> UNLIMITED. Default: OFF) WITH (QUEUE_DELAY = 1000 -- time before Force Review, default 1000 (millisecond), value 0 indicates synchronous transfer, ON_FAILURE = CONTINUE -- when the target file cannot be written: CONTINUE (default) | SHUTDOWN (permission required) --, AUDIT_GUID = <GUID> -- database image used) GO -- change server Audit Object (same as creating format) -- https://msdn.microsoft.com/zh-cn/library/cc280563%28v= SQL .100%29.aspx? F = 255 & MSPPError =-2147217396 alter 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 server audit specification objects -- https://msdn.microsoft.com/zh-cn/library/cc280767%28v= SQL .100%29.aspx? F = 255 & MSPPError =-2147217396 create server audit specification [Audit_Specification_Server] for server audit [Audit_ToFile] -- ADD (FAILED_LOGIN_GROUP) -- Name of the server-level auditable operation group (for example, Logon Failure Review) WITH (STATE = ON) -- allow or forbid audit collection records of this audit specification GO--SQL Server audit action groups and actions-https://msdn.microsoft.com/zh-cn/library/cc280663%28v= SQL .100%29.aspx? F = 255 & MSPPError =-2147217396 -- change server audit specification object (the status of 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 [Reset] for server audit [Audit_ToFile] ADD (batch), ADD (LOGOUT_GROUP), DROP (Batch) WITH (S TATE = ON) GO/* The status of the audit specification must be set to OFF to change the audit specification. Otherwise, an error occurs: Message 33229, level 16, status 1, when the audit specification is disabled for Row 3, the changes to the audit specification must have been 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 =-2147217396 alter 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 database Audit specification object (only create audit 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 [Audit_ToFile] ADD (select on dbo. errorLog BY [dbo]) WITH (STATE = ON) GO -- change the database Audit specification object (audit collection is prohibited) -- https: // Msdn.microsoft.com/zh-cn/library/cc2806451_28v1_ SQL .1001_29.aspx? F = 255 & MSPPError =-2147217396 alter 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]) WITH (STATE = ON) GO -- delete database Audit specification objects (records of this audit specification must be forbidden for Review Collection) -- https://msdn.microsoft.com/zh-cn/library/cc2 80479% 28 v = SQL .100% 29. aspx? F = 255 & MSPPError =-2147217396 alter 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 audit specification [Audit_Specification_Database] GO

-- Delete from dbo. ErrorLog when the table ErrorLog of AdventureWorks2008R2 is deleted -- audit log records are as follows:

-- Query the select * from sys. server_audits -- Audit Object select * from sys. dm_server_audit_status -- select * from sys. server_audit_specifications -- select * from sys. server_audit_specification_details -- select * from sys. database_audit_specifications -- select * from sys. database_audit_specification_details -- select * from sys. dm_audit_actions -- select * from sys. dm_audit_class_type_map -- Review the field ing in the log select * from sys. server_file_audits -- Review the File Information select * from sys. fn_get_audit_file ('e: \ Audit_ToFile _*. sqlaudit ', default, default );