SQL Server 2019登录触发器：授权生效但未记录拒绝登录日志

问题根源 #

原触发器中，虽然先执行了日志插入并COMMIT，但SQL Server的LOGON触发器运行在系统级的隐式事务上下文中，手动执行的COMMIT不会真正提交事务。当后续执行ROLLBACK拒绝登录时，会回滚当前会话的所有操作，包括之前的日志插入，导致拒绝登录的记录丢失。

解决方案 #

要确保拒绝登录的日志被持久化，需要将日志写入逻辑放到独立的事务中，与触发器的事务隔离。可以通过调用存储过程实现，存储过程内的事务不受触发器ROLLBACK的影响。

步骤1：创建日志写入存储过程 #

先创建一个专门处理登录日志插入的存储过程，确保其事务独立：

CREATE OR ALTER PROCEDURE dbo.LogLoginAttempt
    @LoginName NVARCHAR(128),
    @LoginTime DATETIME,
    @IsSuccess BIT,
    @HostName NVARCHAR(128),
    @IPAddress NVARCHAR(48)
WITH EXECUTE AS 'sa'
AS
BEGIN
    SET NOCOUNT ON;
    SET IMPLICIT_TRANSACTIONS OFF; -- 关闭隐式事务，保证事务独立执行

    BEGIN TRY
        BEGIN TRAN;
            INSERT INTO dbo.LOG_TABLE (LoginName, LoginTime, IsSuccess, HostName, IPAddress)
            VALUES (@LoginName, @LoginTime, @IsSuccess, @HostName, @IPAddress);
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        -- 可选：添加错误日志逻辑，记录插入失败的情况
    END CATCH
END;

步骤2：修改登录触发器 #

调整触发器逻辑，先判断登录权限，再调用存储过程记录对应状态的日志，最后执行拒绝操作：

CREATE OR ALTER TRIGGER [trigger_logon_control]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    -- 收集登录相关信息
    DECLARE 
        @LoginName NVARCHAR(128) = ORIGINAL_LOGIN(),
        @LoginTime DATETIME = GETDATE(),
        @IsSuccess BIT = 1,
        @HostName NVARCHAR(128) = HOST_NAME(),
        @IPAddress NVARCHAR(48) = CONVERT(NVARCHAR(48), CONNECTIONPROPERTY('client_net_address'));

    -- 判断是否拒绝登录
    IF ORIGINAL_LOGIN() = 'TEST2'
    BEGIN
        SET @IsSuccess = 0;
        -- 先记录拒绝登录的日志
        EXEC dbo.LogLoginAttempt @LoginName, @LoginTime, @IsSuccess, @HostName, @IPAddress;
        -- 执行拒绝登录操作
        ROLLBACK;
        RETURN;
    END;

    -- 记录成功登录的日志
    EXEC dbo.LogLoginAttempt @LoginName, @LoginTime, @IsSuccess, @HostName, @IPAddress;
END;

关键说明 #

• 存储过程通过SET IMPLICIT_TRANSACTIONS OFF关闭隐式事务，确保自身的COMMIT操作能独立生效，不受触发器后续ROLLBACK的影响。
• 先判断登录权限，再记录对应状态的日志，保证允许和拒绝登录的情况都能被完整记录。
• 需提前创建LOG_TABLE，字段需与存储过程中的插入字段匹配，示例表结构参考：

CREATE TABLE dbo.LOG_TABLE (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    LoginName NVARCHAR(128) NOT NULL,
    LoginTime DATETIME NOT NULL,
    IsSuccess BIT NOT NULL,
    HostName NVARCHAR(128),
    IPAddress NVARCHAR(48)
);

内容的提问来源于stack exchange，提问作者altink