登入觸發程序Logon Triggers

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

登入觸發程序會引發預存程序來回應 LOGON 事件。Logon triggers fire stored procedures in response to a LOGON event. SQL ServerSQL Server執行個體建立使用者工作階段時,就會引發這個事件。This event is raised when a user session is established with an instance of SQL ServerSQL Server. 登入觸發程序會在登入驗證階段結束之後,但在使用者工作階段實際建立之前引發。Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. 因此,從觸發程序內產生且一般會顯示給使用者的所有訊息,例如錯誤訊息和來自 PRINT 陳述式的訊息,都會轉至 SQL ServerSQL Server 錯誤記錄檔。Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL ServerSQL Server error log. 如果驗證失敗,登入觸發程序就不會引發。Logon triggers do not fire if authentication fails.

您可以使用登入觸發程序稽核和控制伺服器工作階段,例如追蹤登入活動、限制登入 SQL ServerSQL Server,或限制特定登入的工作階段數。You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL ServerSQL Server, or limiting the number of sessions for a specific login. 例如,在下列程式碼中,如果登入 SQL ServerSQL Server login_test 已經建立三個使用者工作階段,登入觸發程序就會拒絕該登入對 所起始的登入嘗試。For example, in the following code, the logon trigger denies log in attempts to SQL ServerSQL Server initiated by login login_test if there are already three user sessions created by that login.

USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,  
    CHECK_EXPIRATION = ON;  
GO  
GRANT VIEW SERVER STATE TO login_test;  
GO  
CREATE TRIGGER connection_limit_trigger  
ON ALL SERVER WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK;  
END;  

請注意,LOGON 事件對應於 AUDIT_LOGIN SQL 追蹤事件,該事件可用於 事件通知Note that the LOGON event corresponds to the AUDIT_LOGIN SQL Trace event, which can be used in Event Notifications. 觸發程序與事件通知的主要差別在於,觸發程序會與事件同步引發,而事件通知則是非同步的。The primary difference between triggers and event notifications is that triggers are raised synchronously with events, whereas event notifications are asynchronous. 這表示如果不要建立工作階段,就必須使用登入觸發程序。This means, for example, that if you want to stop a session from being established, you must use a logon trigger. AUDIT_LOGIN 事件的事件通知則無法這麼做。An event notification on an AUDIT_LOGIN event cannot be used for this purpose.

指定第一個和最後一個觸發程序Specifying First and Last Trigger

LOGON 事件可以定義多個觸發程序。Multiple triggers can be defined on the LOGON event. 您可以使用 sp_settriggerorder 系統預存程序,將這些觸發程序的其中任一個指定為對事件第一個或最後一個引發的觸發程序。Any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure. SQL ServerSQL Server 不保證其餘觸發程序的執行順序。does not guarantee the execution order of the remaining triggers.

管理交易Managing Transactions

SQL ServerSQL Server 引發登入觸發程序之前, SQL ServerSQL Server 會建立獨立於任何使用者交易以外的隱含交易。Before SQL ServerSQL Server fires a logon trigger, SQL ServerSQL Server creates an implicit transaction that is independent from any user transaction. 因此,當第一個登入觸發程序開始引發時,交易計數為 1。Therefore, when the first logon trigger starts firing, the transaction count is 1. 等到所有登入觸發程序完成執行之後,交易便認可。After all the logon triggers finish executing, the transaction commits. 就如同其他類型的觸發程序一樣,如果登入觸發程序完成執行時的交易計數為 0, SQL ServerSQL Server 會傳回錯誤。As with other types of triggers, SQL ServerSQL Server returns an error if a logon trigger finishes execution with a transaction count of 0. ROLLBACK TRANSACTION 陳述式會將交易計數重設為 0 (即使該陳述式是從巢狀交易內發出)。The ROLLBACK TRANSACTION statement resets the transaction count to 0, even if the statement is issued inside a nested transaction. COMMIT TRANSACTION 可能會將交易計數遞減為 0。COMMIT TRANSACTION might decrement the transaction count to 0. 因此建議不要從登入觸發程序內發出 COMMIT TRANSACTION 陳述式。Therefore, we advise against issuing COMMIT TRANSACTION statements inside logon triggers.

在登入觸發程序內使用 ROLLBACK TRANSACTION 陳述式時,請考慮下列事項:Consider the following when you are using a ROLLBACK TRANSACTION statement inside logon triggers:

  • 一直到 ROLLBACK TRANSACTION 的時間點所做的任何資料修改都會回復。Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back. 這些修改包括目前觸發程序所做的修改,以及先前觸發程序對相同事件執行時所做的修改。These modifications include those made by the current trigger and those made by previous triggers that executed on the same event. 該特定事件的任何其他觸發程序則不會執行。Any remaining triggers for the specific event are not executed.

  • 目前觸發程序會繼續執行 ROLLBACK 陳述式以後出現的任何其他陳述式。The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement. 如果有任何這些陳述式修改資料,不會回復這些修改。If any of these statements modify data, the modifications are not rolled back.

如果在對 LOGON 事件執行觸發程序時發生下列任一情況,不會建立使用者工作階段:A user session is not established if any one of the following conditions occur during execution of a trigger on a LOGON event:

  • 原始隱含交易回復或失敗。The original implicit transaction is rolled back or fails.

  • 觸發程序主體內引發嚴重性超過 20 的錯誤。An error that has severity greater than 20 is raised inside the trigger body.

停用登入觸發程序Disabling a Logon Trigger

登入觸發程序可以有效地防止所有使用者成功連接到 Database EngineDatabase Engine ,包括 sysadmin 固定伺服器角色的成員。A logon trigger can effectively prevent successful connections to the Database EngineDatabase Engine for all users, including members of the sysadmin fixed server role. 當登入觸發程序防止連接時, sysadmin 固定伺服器角色的成員可以使用專用管理員連接或以最低組態模式 (-f) 啟動 Database EngineDatabase Engine ,藉以進行連接。When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database EngineDatabase Engine in minimal configuration mode (-f). 如需詳細資訊,請參閱 Database Engine Service Startup OptionsFor more information, see Database Engine Service Startup Options.

工作Task 主題Topic
描述如何建立登入觸發程序。Describes how to create logon triggers. 登入觸發程序可以從任何資料庫建立,但會在伺服器層級註冊並儲存在 master 資料庫中。Logon triggers can be created from any database, but are registered at the server level and reside in the master database. CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)
描述如何修改登入觸發程序。Describes how to modify logon triggers. ALTER TRIGGER (Transact-SQL)ALTER TRIGGER (Transact-SQL)
描述如何刪除登入觸發程序。Describes how to delete logon triggers. DROP TRIGGER (Transact-SQL)DROP TRIGGER (Transact-SQL)
描述如何傳回有關登入觸發程序的詳細資訊。Describes how to return information about logon triggers. sys.server_triggers (Transact-SQL)sys.server_triggers (Transact-SQL)

sys.server_trigger_events (Transact-SQL)sys.server_trigger_events (Transact-SQL)
描述如何擷取登入觸發程序事件資料。Describes how to capture logon trigger event data.

另請參閱See Also

DDL 觸發程序DDL Triggers