如何配置SQL Server角色限制Windows认证用户仅通过程序连接数据库？

你的脚本核心问题是撤销了服务器级的CONNECT SQL权限——这会直接阻止该用户建立任何到SQL Server的连接，不管是程序还是SSMS，导致程序也无法正常连接，完全偏离了需求。

要实现“仅允许程序执行CRUD、禁止SSMS登录”，不能从服务器连接权限下手，而是要通过登录触发器判断客户端应用类型，拦截SSMS的连接请求，同时确保用户只有数据库级的最小权限。

步骤1：恢复必要的服务器连接权限 #

先把之前错误撤销的权限恢复，否则任何连接都无法建立：

USE MASTER;
GRANT CONNECT SQL TO [MyDom\myUser];

步骤2：创建登录触发器拦截SSMS连接 #

登录触发器会在用户尝试登录时触发，通过APP_NAME()函数获取客户端应用名称，匹配SSMS的特征（通常以Microsoft SQL Server Management Studio开头），匹配到则拒绝登录：

USE MASTER;
GO
CREATE TRIGGER trg_BlockSSMSLogin
ON ALL SERVER FOR LOGON
AS
BEGIN
    -- 仅对目标用户生效，拦截SSMS连接
    IF ORIGINAL_LOGIN() = 'MyDom\myUser' 
       AND APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'
    BEGIN
        ROLLBACK;
    END
END;
GO

注意：部分SSMS版本的应用名称可能略有差异，可先在SSMS中执行SELECT APP_NAME()确认准确名称，再调整匹配规则。

步骤3：优化用户权限配置 #

你的数据库角色权限逻辑没问题，但可以清理冗余操作，确保权限最小化：

-- 服务器级配置（仅保留必要权限）
USE MASTER;
CREATE USER [MyDom\myUser] FOR LOGIN [MyDom\myUser];
ALTER USER [MyDom\myUser] WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT SQL TO [MyDom\myUser];
-- 若程序不需要查看对象定义，可删除下面这行
-- GRANT VIEW DEFINITION TO [MyDom\myUser];

-- 数据库级配置（仅授予CRUD权限）
USE [myBD];
CREATE ROLE IF NOT EXISTS [myRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [myRole];
ALTER USER [MyDom\myUser] WITH DEFAULT_SCHEMA = dbo;
ALTER ROLE [myRole] ADD MEMBER [MyDom\myUser];

验证方法 #

1. 用SSMS尝试登录MyDom\myUser，会直接弹出“登录失败”提示；
2. 用你的业务程序通过Windows身份验证连接数据库，可正常执行CRUD操作。

进阶安全优化 #

如果你的程序有固定的应用名称，也可以反过来配置：仅允许指定的程序名称连接，拒绝所有其他客户端，安全性更高：

ALTER TRIGGER trg_BlockSSMSLogin
ON ALL SERVER FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'MyDom\myUser' 
       AND APP_NAME() NOT IN ('你的程序应用名称')
    BEGIN
        ROLLBACK;
    END
END;
GO

注意：创建登录触发器需要服务器级的ALTER ANY LOGIN TRIGGER权限，执行脚本的账号需具备该权限。

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