如何阻止拥有db_datawriter角色的用户通过Management Studio连接但允许WinForm应用操作数据

你可以通过以下两种常用方案实现需求，可根据安全要求选择：

方案1：SQL Server登陆触发器（无应用代码修改，适合低安全要求场景） #

该方案通过登陆触发校验客户端应用类型，直接阻止指定用户通过SSMS登陆，无需修改现有WinForms应用代码。

前置准备 #

• 为你的WinForms应用连接字符串添加Application Name参数，设置为唯一标识（如MyCompany_WinForms_App），避免和其他应用重名：

  <!-- 连接字符串示例 -->
  <add name="DbConn" connectionString="Server=你的SQL实例;Database=你的库;Integrated Security=True;Application Name=MyCompany_WinForms_App;" />
  

• 保留一个已登陆的高权限SQL会话，防止触发器配置错误导致所有用户无法登陆。

配置步骤 #

1. 先确认要限制的用户完整登录名，可通过该用户登陆后执行以下语句查询：

   SELECT ORIGINAL_LOGIN();
   

2. 创建并启用登陆触发器：

   CREATE TRIGGER Block_SSMS_For_Target_User
   ON ALL SERVER 
   FOR LOGON
   AS
   BEGIN
       -- 替换为你要限制的Windows用户完整标识
       DECLARE @TargetUser NVARCHAR(128) = N'域名称\目标用户名';
       -- 替换为你之前设置的允许的应用名称
       DECLARE @AllowedApp NVARCHAR(128) = N'MyCompany_WinForms_App';
   
       IF ORIGINAL_LOGIN() = @TargetUser
       BEGIN
           -- 匹配SSMS应用名或者非允许的应用，直接拒绝登陆
           IF APP_NAME() LIKE N'Microsoft SQL Server Management Studio%' OR APP_NAME() <> @AllowedApp
           BEGIN
               ROLLBACK;
           END
       END
   END;
   GO
   ENABLE TRIGGER Block_SSMS_For_Target_User ON ALL SERVER;
   

局限性 #

应用名称可被客户端伪造，若用户自行编写程序将应用名改为你允许的标识，仍可连接数据库操作数据。

方案2：SQL Server应用程序角色（高安全性，适合敏感数据场景） #

该方案从权限层面隔离用户账号和操作权限，用户自身账号无任何数据操作权限，只有WinForms应用激活专用应用角色后才可修改数据，无法被伪造。

配置步骤 #

1. 收回目标用户原有权限：

   USE 你的数据库名称;
   GO
   -- 移除用户的db_datawriter角色
   ALTER ROLE db_datawriter DROP MEMBER [域名称\目标用户名];
   GO
   

2. 创建应用程序角色并分配权限：

   USE 你的数据库名称;
   GO
   -- 创建应用角色，设置仅应用知晓的复杂密码
   CREATE APPLICATION ROLE WinForms_Data_Operate_Role
   WITH PASSWORD = N'自定义的复杂密码', DEFAULT_SCHEMA = dbo;
   GO
   -- 给应用角色分配数据修改权限
   ALTER ROLE db_datawriter ADD MEMBER WinForms_Data_Operate_Role;
   GO
   

3. 修改WinForms应用数据库连接逻辑，连接建立后立即激活应用角色：

   using (SqlConnection conn = new SqlConnection("你的Windows认证连接字符串"))
   {
       conn.Open();
       // 激活应用程序角色
       SqlCommand activateCmd = new SqlCommand("sp_setapprole", conn);
       activateCmd.CommandType = CommandType.StoredProcedure;
       activateCmd.Parameters.AddWithValue("@rolename", "WinForms_Data_Operate_Role");
       activateCmd.Parameters.AddWithValue("@password", "你设置的应用角色密码");
       activateCmd.ExecuteNonQuery();
   
       // 后续所有数据库操作都将继承应用角色的权限
   }
   

优势 #

即使用户通过SSMS登陆自己的Windows账号，也没有任何数据库操作权限，完全规避权限伪造风险。

注意事项 #

• 配置登陆触发器前务必先测试规则匹配逻辑，建议先将ROLLBACK替换为写入日志表的操作，确认规则无误后再开启拦截，避免锁定合法用户
• 应用程序角色的密码建议加密存储在应用配置文件中，不要硬编码到源码内
• 若后续升级SSMS版本，需核对SSMS的APP_NAME是否变化，及时更新触发器匹配规则

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