SQL Server audit (CORE) case

--- Create audit and audit specifications
Use master
Create server audit my_first_audit to file (filepath = 'd: \ SQL \ test \ ', maxsize = 10 MB)
Go
--- Create and configure SQL Server audit objects that can be used for Auditing
Use test
Create Database Audit specification my_aud_spec for server audit my_first_audit add (select, update, insert, delete, exec, references on DBO. t_A by public) --- the database-level audit operation used this time (audit-level operations include server-level, database-level, and audit-level operations)
Go
---- Check whether the audit exists and the existing path
Use master
Select * From SYS. server_file_audits
Use test
-- Existence and status of audit specifications
Select * From SYS. database_audit_specifications
-- Enable audit and audit specifications (that is, whether to allow or disable audit to collect records of this audit Specification .)
Use master
Alter server audit my_first_audit with (State = on)
Go
Use test
Alter Database Audit specification my_aud_spec with (State = on)
Go
------------ Execute related SQL statements
Select * From T_A
Delete from T_A where id = 3
Update T_A set name = 'audit' where id = 2

--- View audit records
Select * From fn_get_audit_file ('d: \ SQL \ test \ * ', null, null)
Go

A complete review case

--- Create Audit Object
Use [Master]

Go

Create server audit [my_first_audit]
To File
(Filepath = n'd: \ test_audit'
, Maxsize = 0 MB --- unlimited File Size
, Max_rolover_files = 2147483647 -- the size of the rolling file is unlimited.
, Reserve_disk_space = off -- disable hard disk space reservation
)
With
(Queue_delay = 1000 --- queue delay time in MS
, On_failure = continue --- do not close the server when reviewing log faults (continue | Shutdown)
)
--- Audit target: file \ Security log \ Application Log
Go
--- Server audit Specification
Use [Master]

Go

Create server audit specification [server_auditspecification]
For server audit [my_first_audit]
Add (backup_restore_group), --- audit operation type
Add (database_object_change_group ),
Add (trace_change_group)

--- Specific audit operation type: see books online
Go

--- Enable server audit specifications
Alter server audit specification [server_auditspecification]
With (State = on)
---- Disable or disable server review specifications
Alter server audit specification [server_auditspecification]
With (State = Off)

--- Enable server Audit Object
Alter server audit [my_first_audit] With (State = on)

--- Disable or disable the server Audit Object
Alter server audit [my_first_audit] With (State = Off)

/* Create a database Audit specification */
Use [db_test]

Go

Create Database Audit specification [database_auditspecification]
For server audit [my_first_audit]
Add (select on Object: [DBO]. [a] by [audit]) --- specifies the Object Name (A) of the audit operation type, object class (object, schema, database), and object architecture (DBO) body name (login name or user name)
--- Specific audit operation type: see books online
Go
--- Enable database Audit specifications
Alter Database Audit specification [database_auditspecification]
With (State = on)
--- Disable or disable the database Audit Specification
Alter Database Audit specification [database_auditspecification]
With (State = Off)