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

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance

使用 SQL ServerSQL Server Audit 來建立伺服器稽核物件。Creates a server audit object using SQL ServerSQL Server Audit. 如需詳細資訊,請參閱 SQL Server Audit (Database Engine)For more information, see SQL Server Audit (Database Engine).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

CREATE SERVER AUDIT audit_name  
{  
    TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }  
    [ WITH ( <audit_options> [ , ...n ] ) ]   
    [ WHERE <predicate_expression> ]  
}  
[ ; ]  
  
<file_options>::=  
{  
        FILEPATH = 'os_file_path'  
    [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]  
    [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]  
    [ , RESERVE_DISK_SPACE = { ON | OFF } ]   
}  
  
<audit_options>::=  
{  
    [   QUEUE_DELAY = integer ]  
    [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ]  
    [ , AUDIT_GUID = uniqueidentifier ]  
}  
  
<predicate_expression>::=  
{  
    [NOT ] <predicate_factor>   
    [ { AND | OR } [NOT ] { <predicate_factor> } ]   
    [,...n ]  
}  
  
<predicate_factor>::=   
    event_field_name { = | < > | ! = | > | > = | < | < = | LIKE } { number | ' string ' }  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

引數Arguments

TO { FILE | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR } 決定稽核目標的位置。TO { FILE | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR } Determines the location of the audit target. 選項有二進位檔案、Windows 應用程式記錄檔或 Windows 安全性記錄檔。The options are a binary file, The Windows Application log, or the Windows Security log. SQL ServerSQL Server 就無法寫入 Windows 安全性記錄檔。cannot write to the Windows Security log without configuring additional settings in Windows. 如需詳細資訊,請參閱 將 SQL Server Audit 事件寫入安全性記錄檔For more information, see Write SQL Server Audit Events to the Security Log.

重要

在 Azure SQL 受控執行個體中,SQL 稽核會在伺服器層級執行。In Azure SQL Managed Instance, SQL Audit works at the server level. 位置只能是 URLEXTERNAL_MONITORLocations can only be URL or EXTERNAL_MONITOR.

FILEPATH ='os_file_path'FILEPATH ='os_file_path'
稽核記錄檔的路徑。The path of the audit log. 檔案名稱是根據稽核名稱和稽核 GUID 所產生。The file name is generated based on the audit name and audit GUID.

MAXSIZE = { max_size }MAXSIZE = { max_size }
指定稽核檔案所能成長的大小上限。Specifies the maximum size to which the audit file can grow. max_size 值必須是整數,而且後面緊接著 MB、GB、TB 或 UNLIMITED。The max_size value must be an integer followed by MB, GB, TB, or UNLIMITED. 您可以為 max_size 指定的大小下限為 2 MB,而上限則為 2,147,483,647 TB。The minimum size that you can specify for max_size is 2 MB and the maximum is 2,147,483,647 TB. 指定了 UNLIMITED 時,檔案會成長到磁碟已滿為止When UNLIMITED is specified, the file grows until the disk is full. (0 也指出 UNLIMITED)。指定低於 2 MB 的值會引發 MSG_MAXSIZE_TOO_SMALL 錯誤。(0 also indicates UNLIMITED.) Specifying a value lower than 2 MB raises the error MSG_MAXSIZE_TOO_SMALL. 預設值為 UNLIMITED。The default value is UNLIMITED.

MAX_ROLLOVER_FILES = { integer | UNLIMITED }MAX_ROLLOVER_FILES ={ integer | UNLIMITED }
除了目前的檔案以外,指定要保留在檔案系統中的檔案數目上限。Specifies the maximum number of files to retain in the file system in addition to the current file. MAX_ROLLOVER_FILES 值必須是整數或 UNLIMITED。The MAX_ROLLOVER_FILES value must be an integer or UNLIMITED. 預設值為 UNLIMITED。The default value is UNLIMITED. 每當稽核重新啟動 (當 Database EngineDatabase Engine 的執行個體重新啟動或者稽核先關閉然後再次開啟時,就可能會發生此情況) 或者由於達到 MAXSIZE 而需要新的檔案時,系統就會評估此參數。This parameter is evaluated whenever the audit restarts (which can happen when the instance of the Database EngineDatabase Engine restarts or when the audit is turned off and then on again) or when a new file is needed because the MAXSIZE has been reached. 評估 MAX_ROLLOVER_FILES 時,如果檔案的數目超過 MAX_ROLLOVER_FILES 設定,系統就會刪除最舊的檔案。When MAX_ROLLOVER_FILES is evaluated, if the number of files exceeds the MAX_ROLLOVER_FILES setting, the oldest file is deleted. 因此,如果 MAX_ROLLOVER_FILES 的設定為 0,每次評估 MAX_ROLLOVER_FILES 設定時,系統都會建立新的檔案。As a result, when the setting of MAX_ROLLOVER_FILES is 0 a new file is created each time the MAX_ROLLOVER_FILES setting is evaluated. 評估 MAX_ROLLOVER_FILES 設定時,系統只會自動刪除一個檔案,所以當您降低 MAX_ROLLOVER_FILES 的值時,除非手動刪除舊的檔案,否則檔案的數目將不會縮減。Only one file is automatically deleted when MAX_ROLLOVER_FILES setting is evaluated, so when the value of MAX_ROLLOVER_FILES is decreased, the number of files does not shrink unless old files are manually deleted. 可以指定的檔案數量上限為 2,147,483,647。The maximum number of files that can be specified is 2,147,483,647.

MAX_FILES =integerMAX_FILES =integer
適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指定可建立的最大稽核檔案數目。Specifies the maximum number of audit files that can be created. 達到此限制時,不會換用第一個檔案。Does not rollover to the first file when the limit is reached. 達到 MAX_FILES 限制時,導致系統產生其他稽核事件的任何動作都會失敗並發生錯誤。When the MAX_FILES limit is reached, any action that causes additional audit events to be generated, fails with an error.

RESERVE_DISK_SPACE = { ON | OFF }RESERVE_DISK_SPACE = { ON | OFF }
這個選項會在磁碟上將檔案預先配置為 MAXSIZE 值。This option pre-allocates the file on the disk to the MAXSIZE value. 只有當 MAXSIZE 不等於 UNLIMITED 時,才會套用它。It applies only if MAXSIZE is not equal to UNLIMITED. 預設值是 OFF。The default value is OFF.

QUEUE_DELAY =integerQUEUE_DELAY =integer
判斷在強制處理稽核動作之前經過的時間長度 (以毫秒為單位)。Determines the time, in milliseconds, that can elapse before audit actions are forced to be processed. 值為 0 表示同步傳遞。A value of 0 indicates synchronous delivery. 可設定的最小查詢延遲值為 1000 (1 秒),這是預設值。The minimum settable query delay value is 1000 (1 second), which is the default. 最大值為 2,147,483,647 (2,147,483.647 秒鐘或是 24 天 20 小時 31 分鐘又 23.647 秒鐘)。The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). 指定無效的數字會引發 MSG_INVALID_QUEUE_DELAY 錯誤。Specifying an invalid number, raises the MSG_INVALID_QUEUE_DELAY error.

ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }
指出如果目標無法寫入稽核記錄,則寫入目標的執行個體應該失敗、繼續還是停止 SQL ServerSQL ServerIndicates whether the instance writing to the target should fail, continue, or stop SQL ServerSQL Server if the target cannot write to the audit log. 預設值為 CONTINUE。The default value is CONTINUE.

CONTINUECONTINUE
SQL ServerSQL Server 作業繼續進行。operations continue. 系統不會保留稽核記錄。Audit records are not retained. 稽核會繼續嘗試記錄事件,而且如果失敗狀況已解決,就會恢復稽核。The audit continues to attempt to log events and resumes if the failure condition is resolved. 選取繼續選項會允許可能違反安全性原則的未稽核活動。Selecting the continue option can allow unaudited activity, which could violate your security policies. 當繼續進行 Database EngineDatabase Engine 作業比維持完整稽核更重要時,請使用此選項。Use this option, when continuing operation of the Database EngineDatabase Engine is more important than maintaining a complete audit.

SHUTDOWNSHUTDOWN
如果 SQL ServerSQL Server 因為任何原因無法將資料寫入稽核目標,則會強制關閉 SQL ServerSQL Server 的執行個體。Forces the instance of SQL ServerSQL Server to shut down, if SQL ServerSQL Server fails to write data to the audit target for any reason. 執行 CREATE SERVER AUDIT 陳述式的登入在 SQL ServerSQL Server 內必須擁有 SHUTDOWN 權限。The login executing the CREATE SERVER AUDIT statement must have the SHUTDOWN permission within SQL ServerSQL Server. 即使稍後已從執行中的登入撤銷 SHUTDOWN 權限,關閉的行為仍會持續。The shutdown behavior persists even if the SHUTDOWN permission is later revoked from the executing login. 如果使用者沒有此權限,則陳述式將會失敗,且將不會建立稽核。If the user does not have this permission, then the statement fails and the audit is not be created. 當稽核失敗可能危害系統的安全性或完整性時,請使用此選項。Use the option when an audit failure could compromise the security or integrity of the system. 如需詳細資訊,請參閱 SHUTDOWNFor more information, see SHUTDOWN.

FAIL_OPERATIONFAIL_OPERATION
如果資料庫動作導致稽核的事件,這些動作就會失敗。Database actions fail if they cause audited events. 雖然不會導致稽核事件的動作可繼續進行,不過也無法發生稽核的事件。Actions, which do not cause audited events can continue, but no audited events can occur. 稽核會繼續嘗試記錄事件,而且如果失敗狀況已解決,就會恢復稽核。The audit continues to attempt to log events and resumes if the failure condition is resolved. 當維持完整稽核比 Database EngineDatabase Engine 的完整存取權更重要時,請使用此選項。Use this option when maintaining a complete audit is more important than full access to the Database EngineDatabase Engine.
適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

AUDIT_GUID =uniqueidentifierAUDIT_GUID =uniqueidentifier
若要支援類似資料庫鏡像等案例,稽核需要一個特定的 GUID,而且此 GUID 要符合鏡像資料庫中找到的 GUID。To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. 當建立稽核之後,就無法再修改 GUID。The GUID cannot be modified after the audit has been created.

predicate_expressionpredicate_expression
適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指定用來判斷是否應該處理事件的述詞運算式。Specifies the predicate expression used to determine if an event should be processed or not. 述詞運算式限制為 3000 個字元,這會限制字串引數。Predicate expressions are limited to 3000 characters, which limits string arguments.

event_field_nameevent_field_name
適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

這是識別述詞來源之事件欄位的名稱。Is the name of the event field that identifies the predicate source. 稽核欄位會在 sys.fn_get_audit_file (Transact-SQL) 中說明。Audit fields are described in sys.fn_get_audit_file (Transact-SQL). 除了 file_nameaudit_file_offsetevent_time 以外的所有欄位都可進行稽核。All fields can be filtered except file_name, audit_file_offset, and event_time.

注意

雖然 action_idclass_type 欄位在 sys.fn_get_audit_file 中是 varchar 類型,但是當它們為可進行篩選的述詞來源時,僅可以搭配數字使用。While the action_id and class_type fields are of type varchar in sys.fn_get_audit_file, they can only be used with numbers when they are a predicate source for filtering. 若要取得搭配 class_type 使用之值的清單,請執行下列查詢:To get the list of values to be used with class_type, execute the following query:

SELECT spt.[name], spt.[number]
FROM   [master].[dbo].[spt_values] spt
WHERE  spt.[type] = N'EOD'
ORDER BY spt.[name];

numbernumber
適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

這是包含 decimal 的任何數值類型。Is any numeric type including decimal. 限制為缺少可用的實體記憶體,或是數字太大而不能表示為 64 位元整數。Limitations are the lack of available physical memory or a number that is too large to be represented as a 64-bit integer.

' string '' string '
適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

ANSI 或 Unicode 字串 (依述詞比較的需求而定)。Either an ANSI or Unicode string as required by the predicate compare. 不會針對述詞比較函數執行隱含字串類型轉換。No implicit string type conversion is performed for the predicate compare functions. 傳遞錯誤的類型會產生錯誤。Passing the wrong type results in an error.

備註Remarks

當建立伺服器稽核之後,它就會處於停用狀態。When a server audit is created, it is in a disabled state.

CREATE SERVER AUDIT 陳述式位於交易的範圍內。The CREATE SERVER AUDIT statement is in a transaction's scope. 如果回復交易,也會回復此陳述式。If the transaction is rolled back, the statement is also rolled back.

權限Permissions

若要建立、更改或卸除伺服器稽核,主體需要使用 ALTER ANY SERVER AUDIT 或 CONTROL SERVER 權限。To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

當您將稽核資訊儲存到檔案時,為了避免遭到篡改,您可以限制對檔案位置的存取:When you are saving audit information to a file, to help prevent tampering, restrict access to the file location.

範例Examples

A.A. 建立具有檔案目標的伺服器稽核Creating a server audit with a file target

下列範例會建立稱為 HIPAA_Audit 的伺服器稽核,並將二進位檔案當做目標而且不指定任何選項。The following example creates a server audit called HIPAA_Audit with a binary file as the target and no options.

CREATE SERVER AUDIT HIPAA_Audit  
    TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );  

B.B. 建立具有 Windows 應用程式記錄檔目標 (含選項) 的伺服器稽核Creating a server audit with a Windows Application log target with options

下列範例會建立稱為 HIPAA_Audit 的伺服器稽核,並包含針對 Windows 應用程式記錄檔所設定的目標。The following example creates a server audit called HIPAA_Audit with the target set for the Windows Application log. 每秒鐘都會寫入此佇列,並在失敗時關閉 SQL ServerSQL Server 引擎。The queue is written every second and shuts down the SQL ServerSQL Server engine on failure.

CREATE SERVER AUDIT HIPAA_Audit  
    TO APPLICATION_LOG  
    WITH ( QUEUE_DELAY = 1000,  ON_FAILURE = SHUTDOWN);  

C.C. 建立包含 WHERE 子句的伺服器稽核Creating a server audit containing a WHERE clause

下列範例會建立資料庫、結構描述和兩個範例資料表。The following example creates a database, schema, and two tables for the example. 名為 DataSchema.SensitiveData 的資料表將包含機密資料,而且此資料表的存取權必須記錄在稽核中。The table named DataSchema.SensitiveData contains confidential data and access to the table must be recorded in the audit. 名為 DataSchema.GeneralData 的資料表則不包含機密資料。The table named DataSchema.GeneralData does not contain confidential data. 資料庫稽核規格會稽核 DataSchema 結構描述中所有物件的存取權。The database audit specification audits access to all objects in the DataSchema schema. 伺服器稽核是使用 WHERE 子句所建立,這個子句會將伺服器稽核限制為只有 SensitiveData 資料表。The server audit is created with a WHERE clause that limits the server audit to only the SensitiveData table. 伺服器稽核會假設稽核資料夾存在 C:\SQLAudit 中。The server audit presumes an audit folder exists at C:\SQLAudit.

CREATE DATABASE TestDB;  
GO  
USE TestDB;  
GO  
CREATE SCHEMA DataSchema;  
GO  
CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);  
GO  
CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);  
GO  
-- Create the server audit in the master database  
USE master;  
GO  
CREATE SERVER AUDIT AuditDataAccess  
    TO FILE ( FILEPATH ='C:\SQLAudit\' )  
    WHERE object_name = 'SensitiveData' ;  
GO  
ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);  
GO  
-- Create the database audit specification in the TestDB database  
USE TestDB;  
GO  
CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]  
FOR SERVER AUDIT [AuditDataAccess]   
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])  
WITH (STATE = ON);  
GO  
-- Trigger the audit event by selecting from tables  
SELECT ID, DataField FROM DataSchema.GeneralData;  
SELECT ID, DataField FROM DataSchema.SensitiveData;  
GO  
-- Check the audit for the filtered content  
SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);  
GO  

另請參閱See Also

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)
建立伺服器稽核與伺服器稽核規格Create a Server Audit and Server Audit Specification