如何在SQL Server中限制存储过程执行被拒的更新/删除操作

已执行的权限操作 #

首先执行了以下语句，禁止SQLEditorSpecificUser直接操作sph_syslog表：

DENY INSERT, UPDATE, DELETE ON OBJECT::sph_syslog TO SQLEditorSpecificUser;

随后又授予该用户dbo架构的高权限：

GRANT CONTROL ON SCHEMA::dbo TO SQLEditorSpecificUser;
GRANT ALTER ON SCHEMA::dbo TO SQLEditorSpecificUser;

问题现象 #

该用户创建的存储过程AshishTest仍能成功更新sph_syslog表，存储过程代码如下：

ALTER PROCEDURE [AshishTest]
AS
BEGIN
    UPDATE sph_syslog 
    SET duration = 8285  
    WHERE id = 1372042 
END

问题原因 #

SQL Server中存储过程默认使用所有者权限上下文（EXECUTE AS OWNER）执行，也就是存储过程的所有者（通常是dbo）拥有操作表的权限，即使调用者被DENY了直接操作权限，通过存储过程执行时会继承所有者的权限，从而绕开限制。另外，授予用户CONTROL ON SCHEMA::dbo权限后，用户拥有该架构下几乎所有对象的管理权限，这也会覆盖之前的DENY设置。

解决方案 #

要实现直接和间接都禁止用户修改sph_syslog表，需要按以下步骤操作：

1. 收回用户的架构高权限
   先撤销之前授予的架构控制和修改权限，消除用户绕开限制的基础：

   REVOKE CONTROL ON SCHEMA::dbo FROM SQLEditorSpecificUser;
   REVOKE ALTER ON SCHEMA::dbo FROM SQLEditorSpecificUser;
   

2. 确保DENY权限覆盖所有操作场景
   执行DENY语句，确保用户无法通过任何途径获得操作权限：

   DENY INSERT, UPDATE, DELETE ON OBJECT::sph_syslog TO SQLEditorSpecificUser;
   

3. 修正存储过程的执行上下文（若需保留用户创建存储过程的权限）
   如果必须让用户拥有创建存储过程的权限，需要修改存储过程，强制使用调用者的权限执行，这样用户的DENY权限会生效：

   ALTER PROCEDURE [AshishTest]
   WITH EXECUTE AS CALLER
   AS
   BEGIN
       UPDATE sph_syslog 
       SET duration = 8285  
       WHERE id = 1372042 
   END
   

   此时再执行该存储过程，会因为用户被DENY了UPDATE权限而失败。

如果不需要用户创建存储过程的权限，直接执行前两步即可彻底禁止用户直接或间接修改目标表。

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