如何配置SQL Server数据库仅允许指定用户访问并排查异常连接

针对你遇到的**空登录名主体持有128个连接并导致CPU占用率飙升至100%**的问题，结合限制数据库仅对指定WEB用户开放的需求，我整理了一套完整的配置方案：

1. 先回收数据库公共角色的默认权限 #

默认情况下，所有登录账户都会继承public角色的权限，这是未授权主体（包括空登录）能连接数据库的核心原因。先执行以下语句回收公共角色的连接权限：

USE [你的目标数据库名];
REVOKE CONNECT TO public;

注意：执行此操作后，除了后续明确授予权限的用户，其他所有主体都无法再连接到该数据库。

2. 仅给指定WEB用户授予连接及必要操作权限 #

为你的WEB访问专属用户明确分配数据库连接权限，同时按需添加业务所需的操作权限：

USE [你的目标数据库名];
-- 授予连接权限
GRANT CONNECT TO [你的WEB用户名];
-- 按需添加数据操作权限，示例为增删改查
GRANT SELECT, INSERT, UPDATE, DELETE ON [你的业务表/视图] TO [你的WEB用户名];

3. 清理当前存在的异常空登录连接 #

在配置权限前，先终止已有的空登录连接，缓解CPU压力：

-- 先查询空登录的会话ID
SELECT spid, loginame, status, cmd 
FROM sys.sysprocesses 
WHERE dbid = DB_ID('你的目标数据库名') AND loginame IS NULL;

-- 替换实际查询到的spid，终止异常连接
KILL [目标spid];

小提示：如果空连接持续生成，建议检查应用端连接字符串是否配置错误，或是否存在未授权的Windows匿名访问设置。

4. 额外安全加固（可选） #

• 清理无用登录账户：在SQL Server实例层面，禁用或删除不需要的登录账户，减少潜在风险。
• 创建登录触发器做精准拦截：如果需要更严格的访问控制，可以创建实例级登录触发器，直接拒绝非指定用户的登录请求：

CREATE TRIGGER RestrictUnauthorizedLogin
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    -- 仅允许指定WEB用户和必要管理员账户登录
    IF ORIGINAL_LOGIN() NOT IN ('你的WEB用户名', '管理员账户名')
    BEGIN
        ROLLBACK;
    END
END;

注意：创建触发器前，请确保有备用管理员登录方式，避免被意外锁定在实例外。

5. 验证配置效果 #

执行你之前的连接统计语句，确认只有指定WEB用户存在连接，空登录的异常连接不再出现：

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as 'Number Of Connections', loginame as LoginName 
FROM sys.sysprocesses 
WHERE dbid > 0 
GROUP BY dbid, loginame;

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