登录触发器

  • 项目

适用于：SQL ServerAzure SQL 托管实例

登录触发器将为响应 LOGON 事件而激发存储过程。 与 SQL Server 实例建立用户会话时将引发此事件。 登录触发器在登录的身份验证阶段完成后且用户会话建立前触发。 因此，所有源自触发器内部且通常会传递给用户的消息（如错误消息和来自 PRINT 语句的消息）会转移到 SQL Server 错误日志。 如果身份验证失败，登录触发器不会触发。

可以使用登录触发器来审核和控制服务器会话，例如通过跟踪登录活动、限制 SQL Server 的登录名或限制特定登录名的会话数。 例如，在以下代码中，如果登录名 login_test 已经创建了三个用户会话，登录触发器将拒绝由该登录名发起的 SQL Server 登录尝试。

USE master;
GO

CREATE LOGIN login_test
WITH PASSWORD = N'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 N'login_test'
FOR LOGON AS BEGIN
    IF ORIGINAL_LOGIN() = N'login_test'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
            AND original_login_name = N'login_test') > 3
    ROLLBACK;
END;

LOGON 事件对应于 AUDIT_LOGIN SQL 跟踪事件，该事件可在事件通知中使用。 触发器与事件通知的主要区别在于触发器随事件同步引发，而事件通知是异步的。 也就是说，例如，如果要停止建立会话，则必须使用登录触发器。 AUDIT_LOGIN 事件的事件通知不能用于此目的。

指定第一个和最后一个触发器

可以对 LOGON 事件定义多个触发器。 通过使用 sp_settriggerorder 系统存储过程，可以将这些触发器中的任何一个指定为针对某事件激发的第一个或最后一个触发器。 SQL Server 不保证其余触发器的执行顺序。

管理事务

SQL Server 激发登录触发器之前，SQL Server 会创建独立于任何用户事务的隐式事务。 因此，第一个登录触发器开始激发时，事务计数为 1。 所有登录触发器完成执行后，将提交事务。 与其他类型的触发器一样，如果登录触发器完成执行后事务计数为 0，SQL Server 将返回错误。 ROLLBACK TRANSACTION 语句会将事务计数重置为 0，即使该语句在嵌套事务中执行也会如此。 COMMIT TRANSACTION 可能会将事务计数递减到 0。 因此，建议不要在登录触发器中发出 COMMIT TRANSACTION 语句。

如果要在登录触发器中使用 ROLLBACK TRANSACTION 语句，请注意以下事项：

  • 将回退对 ROLLBACK TRANSACTION 点所做的任何数据修改。 这些修改包括当前触发器所做的修改以及对同一事件执行的先前触发器所做的修改。 此特定事件的任何其余触发器将不会执行。

  • 当前触发器继续执行显示在 ROLLBACK 语句之后的所有剩余语句。 如果这些语句中的任意语句修改数据，则不回退这些修改。

如果在针对 LOGON 事件执行触发器的过程中满足下列任何一个条件，将不会建立用户会话：

  • 原始隐式事务回滚或失败。
  • 触发器正文中出现严重级别大于 20 的错误。

禁用登录触发器

登录触发器可以有效地阻止所有用户（包括 数据库引擎 sysadmin 固定服务器角色的成员）与 的成功连接。 在登录触发器正在阻止连接时， sysadmin 固定服务器角色的成员可通过使用专用管理员连接，或者通过以最小配置模式 (-f) 启动 数据库引擎 ，来进行连接。 有关详细信息，请参阅数据库引擎服务启动选项。

相关内容