SQL Server Audit 記錄SQL Server Audit Records

08/03/2017

適用範圍：Applies to: SQL ServerSQL Server (所有支援的版本) SQL ServerSQL Server (all supported versions) 適用範圍：Applies to: SQL ServerSQL Server (所有支援的版本) SQL 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 Server。SQL 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 Audit。For 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_information 和 statement 值傳回 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_Time、 action_id 和 session_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.