创建SQL Server登录触发器报错：因触发器导致登录失败，请排查问题

报错原因分析 #

出现“logon failed for login due to trigger”是因为登录触发器执行时发生错误，导致SQL Server阻断了登录请求。你的代码主要存在以下几个问题：

1. 非空约束冲突 #

LoginAudit表中HostName和ApplicationName被设为NOT NULL，但某些登录场景下（比如SQL Server自身服务账号登录、部分客户端登录），EVENTDATA()返回的这两个字段值可能为空，插入时会违反非空约束，触发错误。

2. 数据库上下文未明确 #

服务器级登录触发器默认运行在master数据库上下文下，如果LoginAudit表创建在其他用户数据库中，触发器执行INSERT操作时会找不到该表，导致失败。

3. 权限不足 #

• 创建服务器级触发器需要ALTER ANY SERVER TRIGGER权限。
• 登录账号需要拥有LoginAudit表所在数据库的CONNECT权限，以及对该表的INSERT权限，否则触发器执行插入操作时会因权限不足报错。

修复后的代码 #

-- 先删除原有触发器和表（如果存在）
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'AuditLogins')
DROP TRIGGER AuditLogins ON ALL SERVER;
GO

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'LoginAudit')
DROP TABLE LoginAudit;
GO

-- 创建审计表，修改允许为空的字段
CREATE TABLE LoginAudit (
    LoginAuditID INT PRIMARY KEY IDENTITY(1,1),
    EventType NVARCHAR(128) NOT NULL,
    LoginName NVARCHAR(128) NOT NULL,
    HostName NVARCHAR(128) NULL, -- 改为允许为空
    ApplicationName NVARCHAR(128) NULL, -- 改为允许为空
    LogonTime DATETIME NOT NULL
);
GO

-- 确保登录账号有INSERT权限（替换为实际需要授权的登录名）
GRANT INSERT ON LoginAudit TO [YourLoginName];
GO

-- 创建服务器级登录触发器，明确指定表的完整路径（假设表在master库）
CREATE TRIGGER AuditLogins
ON ALL SERVER
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回额外结果集影响登录流程
    DECLARE @EventData xml
    SET @EventData = EVENTDATA()
    
    -- 使用ISNULL处理可能为空的字段，或者直接插入NULL
    INSERT INTO master.dbo.LoginAudit (EventType, LoginName, HostName, ApplicationName, LogonTime)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
        @EventData.value('(/EVENT_INSTANCE/HostName)[1]', 'nvarchar(128)'),
        @EventData.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(128)'),
        GETDATE()
    )
END;
GO

额外注意事项 #

• 如果LoginAudit表不在master库，需要将master.dbo.LoginAudit替换为实际的[数据库名].[架构名].[表名]。
• 测试触发器时，建议先用拥有高权限的账号（比如sa）登录，确保触发器正常运行后再测试普通账号。
• 可以通过查看SQL Server错误日志，获取触发器执行失败的具体错误信息，进一步排查问题。

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