登录触发器Logon Triggers

适用对象: yesSQL ServeryesAzure SQL 数据库(仅限托管实例)noAzure SQL 数据仓库no并行数据仓库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 Trace 事件,该事件可在 事件通知中使用。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 Engine sysadmin 固定服务器角色的成员)与 的成功连接。A logon trigger can effectively prevent successful connections to the 数据库引擎Database Engine for all users, including members of the sysadmin fixed server role. 在登录触发器正在阻止连接时, sysadmin 固定服务器角色的成员可通过使用专用管理员连接,或者通过以最小配置模式 (-f) 启动 数据库引擎Database 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 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