存储过程权限校验逻辑修正及多表写入事务原子性保障咨询

1. 首先删除错误的前置校验逻辑 #

你原来写的INFORMATION_SCHEMA查询逻辑是问题的根源：SQL Server默认按调用者的安全上下文查询系统视图，所以调用用户没有表权限时查询结果必然不符合预期，直接删除这段校验即可。
存储过程删除校验后能正常执行的原因是SQL Server的所有权链机制：只要存储过程和目标表属于同一个架构、且所有者相同，调用者仅需要存储过程的EXECUTE权限，不需要持有表的任何权限，天然满足你要求的「调用用户无需拥有三张表权限」的要求。

2. 原子性保证核心逻辑无需额外修改 #

你已经把两个INSERT操作放在同一个显式事务中，SQL Server的事务天生满足ACID特性，完全可以保证两个写入要么同时成功、要么同时回滚：

• 执行过程中任何错误（包括表被重命名、权限被收回、字段结构变更、服务器断连等）都会触发CATCH逻辑，事务会完全回滚，不会出现部分写入的情况
• 哪怕是COMMIT瞬间服务器断电/断连，SQL Server重启后会自动回滚未完成的事务，不会残留半提交的数据

3. 优化异常处理逻辑避免二次报错 #

建议在CATCH块回滚事务前增加事务状态判断，避免极端场景下无事务可回滚时抛出二次错误，优化后的完整代码如下：

ALTER PROCEDURE [INV].[Z]
AS
BEGIN
    SET NOCOUNT ON; -- 可选添加，减少不必要的网络传输
    BEGIN TRY
        BEGIN TRANSACTION
            -- 此处替换为你从表A读取数据写入B、C的实际业务逻辑即可
            INSERT INTO [INV].[B] ( col1,  col2) VALUES (1, 2)
            INSERT INTO [INV].[C] ( col3,  col4) VALUES (1, 2)
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- 先判断事务状态再回滚，避免无效回滚报错
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION
        
        SELECT 'Please report the following table for support:'
        SELECT
            ERROR_MESSAGE()   as ErrorMessage,
            ERROR_PROCEDURE() as ErrorProcedure,
            ERROR_LINE()      as ErrorLine,
            ERROR_STATE()     as ErrorState,
            ERROR_SEVERITY()  as ErrorSeverity,
            ERROR_NUMBER()    as ErrorNumber
    END CATCH
END
GO

4. 可选加固方案（应对所有权断裂场景） #

如果担心后续架构所有者变更导致所有权链断裂，可以在存储过程定义中指定执行上下文为所有者，进一步确保存储过程自身有权限访问表：

ALTER PROCEDURE [INV].[Z]
WITH EXECUTE AS OWNER -- 仅需添加这一行
AS
-- 后续逻辑和上面的优化版保持一致即可

该配置下存储过程执行时会使用所属架构所有者的安全上下文，只要所有者持有三张表的读写权限，无论调用者权限如何都能正常执行。

注意事项 #

不要自行添加前置的表存在/权限校验逻辑：这类校验和实际执行之间存在时间差，可能出现校验通过后、执行前表被修改的竞争问题，反而不如直接执行逻辑、靠事务回滚保证原子性可靠。

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