SQL Server登录故障：Grafana账号因Logon触发器执行失败

问题场景 #

删除限制特定IP登录的服务器触发器后，启用LogonAuditTrigger登录审计触发器时，grafana账号登录提示Error logging in with the username 'grafana' due to trigger execution；禁用触发器后grafana可正常登录，需保留审计功能同时解决登录问题。

当前触发器代码：

ALTER TRIGGER [LogonAuditTrigger] /* Creates trigger for logons */
ON ALL SERVER 
FOR LOGON
AS

BEGIN
DECLARE @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@ClientHost varchar(50),
@LoginType varchar(50),
@HostName varchar(50),
@AppName varchar(500)

SET @LogonTriggerData = eventdata()

SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()

INSERT INTO [LogonAudit].[dbo].[LogonAuditing]
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost

END

可能原因 #

1. 权限不足：grafana账号无LogonAudit.dbo.LogonAuditing表的INSERT权限，触发器执行插入操作失败，导致登录被中断（SQL Server登录触发器默认会因执行失败阻止登录）。
2. 字段长度溢出：AppName定义为varchar(500)，若grafana的应用名称长度超过500字符，会触发插入失败。
3. 残留触发器干扰：之前删除的IP限制触发器未彻底清除，仍存在服务器级触发器冲突。

修复方案 #

方案1：授权grafana账号审计表写入权限 #

这是最直接的解决方式，确保触发器能正常插入审计记录：

USE LogonAudit;
-- 授予INSERT权限
GRANT INSERT ON dbo.LogonAuditing TO [grafana];

方案2：给触发器添加错误处理逻辑 #

即使审计插入失败，也不阻止用户登录，同时可记录错误信息（可选）：
修改后的触发器代码：

ALTER TRIGGER [LogonAuditTrigger]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @LogonTriggerData xml,
            @EventTime datetime,
            @LoginName varchar(50),
            @ClientHost varchar(50),
            @HostName varchar(50),
            @AppName varchar(500);

    SET @LogonTriggerData = eventdata();

    SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
    SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)');
    SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)');
    SET @HostName = HOST_NAME();
    SET @AppName = APP_NAME();

    -- 加入TRY/CATCH捕获错误，避免登录中断
    BEGIN TRY
        INSERT INTO [LogonAudit].[dbo].[LogonAuditing]
        (SessionId, LogonTime, HostName, ProgramName, LoginName, ClientHost)
        SELECT @@spid, @EventTime, @HostName, @AppName, @LoginName, @ClientHost;
    END TRY
    BEGIN CATCH
        -- 可选：将错误信息写入另一个错误日志表
        -- INSERT INTO LogonAudit.dbo.TriggerErrors (...) SELECT ...;
    END CATCH
END

方案3：检查并清理残留服务器触发器 #

确认是否存在未彻底删除的旧触发器：

-- 查询所有服务器级触发器
SELECT name, is_disabled, create_date, modify_date 
FROM sys.server_triggers;

若发现之前的IP限制触发器，执行删除：

DROP TRIGGER [旧触发器名称] ON ALL SERVER;

验证步骤 #

1. 启用LogonAuditTrigger触发器：

ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER;

2. 使用grafana账号尝试登录SQL Server，确认登录成功。
3. 查询审计表，验证grafana的登录记录是否正常写入：

SELECT * FROM LogonAudit.dbo.LogonAuditing WHERE LoginName = 'grafana';

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