如何让特定SQL用户登录后自动启用SNAPSHOT隔离级别？

问题分析 #

你尝试用LOGON触发器为特定用户设置快照隔离级别但未生效，测试时查询仍被阻塞，核心原因是原触发器中多余的COMMIT;语句：LOGON触发器运行在系统隐式事务中，显式提交会导致触发器执行异常，后续的SET TRANSACTION ISOLATION LEVEL无法生效。

解决方案 #

方案1：修复LOGON触发器 #

移除触发器中的COMMIT;语句，确保隔离级别设置命令正常执行：

USE master;
GO

CREATE OR ALTER TRIGGER E_LOGON
ON ALL SERVER WITH EXECUTE AS N'sa' 
FOR LOGON
AS  
BEGIN  
    IF ORIGINAL_LOGIN() = N'CNX_USER_TEST'
    BEGIN
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    END;
END;  
GO

验证方式：用CNX_USER_TEST登录后执行DBCC USEROPTIONS;，查看isolation level是否为snapshot。

方案2：数据库级CONNECT触发器（SQL Server 2016+） #

如果LOGON触发器仍有问题，可在目标数据库创建CONNECT触发器，当用户连接到该数据库时自动设置隔离级别：

USE DB_SOURCE;
GO

CREATE OR ALTER TRIGGER E_CONNECT
ON DATABASE
FOR CONNECT
AS
BEGIN
    IF ORIGINAL_LOGIN() = N'CNX_USER_TEST'
    BEGIN
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    END;
END;
GO

方案3：ODBC连接字符串直接配置 #

无需触发器，在ODBC连接字符串中指定快照隔离级别（对应数值5）：

Driver={ODBC Driver 17 for SQL Server};Server=你的服务器地址;Database=DB_SOURCE;Uid=CNX_USER_TEST;Pwd=foo;Transaction Isolation Level=5;

验证测试 #

1. 在窗口1执行：

USE DB_SOURCE;
BEGIN TRAN;
UPDATE T SET C = C + 1;

2. 用CNX_USER_TEST登录新窗口执行：

SELECT * FROM T;

此时查询会返回更新前的快照数据（值为1,2,3），不会被阻塞。

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