SQL Server Audit 記錄SQL Server Audit Records

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

SQL ServerSQL Server Audit 功能可讓您稽核伺服器層級和資料庫層級的事件群組和事件。The SQL ServerSQL Server Audit feature enables you to audit server-level and database-level groups of events and events. 如需詳細資訊,請參閱 SQL Server Audit (Database Engine)For more information, see SQL Server Audit (Database Engine). 第 1 課:建立 Windows Azure 儲存體物件SQL ServerSQL ServerSQL ServerSQL Server.

稽核是由零或多個稽核動作項目所組成,這些項目會記錄到稽核 「目標」 (Target)。Audits consist of zero or more audit action items, which are recorded to an audit target. 稽核目標可以是二進位檔案、Windows 應用程式事件記錄檔或 Windows 安全性事件記錄檔。The audit target can be a binary file, the Windows Application event log, or the Windows Security event log. 傳送給目標的記錄包含下表所述的項目:The records sent to the target can contain the elements described in the following table:

資料行名稱Column name 描述Description 類型Type 永遠可使用Always available
event_timeevent_time 可稽核的動作引發時的日期/時間。Date/time when the auditable action is fired. datetime2datetime2 Yes
sequence_nosequence_no 追蹤單一稽核記錄中太長而無法納入稽核寫入緩衝區內的記錄順序。Tracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits. intint Yes
action_idaction_id 動作的識別碼ID of the action

提示:若要使用 action_id 作為述詞,您必須將它從字元字串轉換為數值。Tip: To use action_id as a predicate it must be converted from a character string to a numeric value. 如需詳細資訊,請參閱 針對 action_id/class_type 述詞篩選 SQL Server AuditFor more information, see Filter SQL Server Audit on action_id / class_type predicate.
varchar(4)varchar(4) Yes
succeededsucceeded 指出觸發稽核事件之動作的權限檢查成功還是失敗。Indicates whether or not the permission check of the action triggering the audit event succeeded or failed. bitbit
-1 = 成功,- 1 = Success,
0 = 失敗0 = Fail
Yes
permission_bitmaskpermission_bitmask 當適用時,顯示已授與、拒絕或撤銷的權限When applicable, shows the permissions that were granted, denied, or revoked bigintbigint No
is_column_permissionis_column_permission 指出資料行層級權限的旗標Flag indicating a column level permission bitbit
- 1 = True,- 1 = True,
0 = False0 = False
No
session_idsession_id 事件發生所在之工作階段的識別碼。ID of the session on which the event occurred. intint Yes
server_principal_idserver_principal_id 動作執行所在之登入環境的識別碼。ID of the login context that the action is performed in. intint Yes
database_principal_iddatabase_principal_id 動作執行所在之資料庫使用者環境的識別碼。ID of the database user context that the action is performed in. intint No
object_idobject_id 稽核發生所在之實體的主要識別碼。The primary ID of the entity on which the audit occurred. 此識別碼可以是:This ID can be:

伺服器物件server objects

資料庫databases

資料庫物件database objects

結構描述物件schema objects
intint No
target_server_principal_idtarget_server_principal_id 套用可稽核之動作的伺服器主體。Server principal that the auditable action applies to. intint Yes
target_database_principal_idtarget_database_principal_id 套用可稽核之動作的資料庫主體。Database principal that the auditable action applies to. intint No
class_typeclass_type 稽核發生所在之可稽核的實體類型。Type of auditable entity that the audit occurs on. varchar(2)varchar(2) Yes
session_server_principal_namesession_server_principal_name 工作階段的伺服器主體。Server principal for the session. sysnamesysname Yes
server_principal_nameserver_principal_name 目前的登入。Current login. sysnamesysname Yes
server_principal_sidserver_principal_sid 目前的登入 SID。Current login SID. varbinaryvarbinary Yes
database_principal_namedatabase_principal_name 目前的使用者。Current user. sysnamesysname No
target_server_principal_nametarget_server_principal_name 動作的目標登入。Target login of the action. sysnamesysname No
target_server_principal_sidtarget_server_principal_sid 目標登入的 SID。SID of the target login. varbinaryvarbinary No
target_database_principal_nametarget_database_principal_name 動作的目標使用者。Target user of the action. sysnamesysname No
server_instance_nameserver_instance_name 稽核發生所在的伺服器執行個體名稱。Name of the server instance where the audit occurred. 使用標準的 machine\instance 格式。Uses the standard machine\instance format. nvarchar(120)nvarchar(120) Yes
database_namedatabase_name 動作發生所在的資料庫環境。The database context in which the action occurred. sysnamesysname No
schema_nameschema_name 動作發生所在的結構描述環境。The schema context in which the action occurred. sysnamesysname No
object_nameobject_name 稽核發生所在之實體的名稱。The name of the entity on which the audit occurred. 此名稱可以是:This name can be:

伺服器物件server objects

資料庫databases

資料庫物件database objects

結構描述物件schema objects

TSQL 陳述式 (如果有的話)TSQL statement (if any)
sysnamesysname No
陳述式statement TSQL 陳述式 (如果有的話)TSQL statement (if any) nvarchar(4000)nvarchar(4000) No
additional_informationadditional_information 有關儲存為 XML 之事件的任何其他資訊。Any additional information about the event, stored as XML. nvarchar(4000)nvarchar(4000) No

備註Remarks

某些動作不會填入資料行的值,因為它可能不適用於此動作。Some actions do not populate a column's value because it might be non-applicable to the action.

SQL ServerSQL Server Audit 會將字元欄位的 4000 個字元資料儲存在稽核記錄中。Audit stores 4000 characters of data for character fields in an audit record. 當從可稽核的動作傳回的 additional_informationstatement 值傳回 4000 個以上的字元時, sequence_no 資料行會用來將多筆記錄寫入單一稽核動作的稽核報表中,以記錄這些資料。When the additional_information and statement values returned from an auditable action return more than 4000 characters, the sequence_no column is used to write multiple records into the audit report for a single audit action to record this data. 此程序如下:The process is as follows:

  • statement 資料行分成 4000 個字元。The statement column is divided into 4000 characters.

  • SQL ServerSQL Server Audit 會寫成具有部分資料之稽核記錄的第一個資料列。Audit writes as the first row for the audit record with the partial data. 所有其他欄位會在每一個資料列中重複。All the other fields are duplicated in each row.

  • sequence_no 值會遞增。The sequence_no value is incremented.

  • 重複執行此程序,直到記錄所有資料為止。This process is repeated until all the data is recorded.

您可以使用 sequence_no 值、 event_Timeaction_idsession_id 資料行按順序讀取資料列來識別此動作,以便連接資料。You can connect the data by reading the rows sequentially using the sequence_no value, and the event_Time, action_id and session_id columns to identify the action.

CREATE SERVER AUDIT (Transact-SQL)CREATE SERVER AUDIT (Transact-SQL)

ALTER SERVER AUDIT (Transact-SQL)ALTER SERVER AUDIT (Transact-SQL)

DROP SERVER AUDIT (Transact-SQL)DROP SERVER AUDIT (Transact-SQL)

CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)

ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)

DROP SERVER AUDIT SPECIFICATION (Transact-SQL)DROP SERVER AUDIT SPECIFICATION (Transact-SQL)

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)

DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)

ALTER AUTHORIZATION (Transact-SQL)ALTER AUTHORIZATION (Transact-SQL)

sys.fn_get_audit_file (Transact-SQL)sys.fn_get_audit_file (Transact-SQL)

sys.server_audits (Transact-SQL)sys.server_audits (Transact-SQL)

sys.server_file_audits (Transact-SQL)sys.server_file_audits (Transact-SQL)

sys.server_audit_specifications (Transact-SQL)sys.server_audit_specifications (Transact-SQL)

sys.server_audit_specification_details (Transact-SQL)sys.server_audit_specification_details (Transact-SQL)

sys.database_audit_specifications (Transact-SQL)sys.database_audit_specifications (Transact-SQL)

sys.database_audit_specification_details (Transact-SQL)sys.database_audit_specification_details (Transact-SQL)

sys.dm_server_audit_status (Transact-SQL)sys.dm_server_audit_status (Transact-SQL)

sys.dm_audit_actions (Transact-SQL)sys.dm_audit_actions (Transact-SQL)

sys.dm_audit_class_type_map (Transact-SQL)sys.dm_audit_class_type_map (Transact-SQL)