使用ALTER SCHEMA TRANSFER转移对象后权限丢失问题咨询

我们每次ALTER存储过程时，会通过添加“_V1”“_V2”这类后缀创建新版本，以此保障应用和数据库的向后兼容性。但这导致旧版本代码大量累积，于是采用ALTER SCHEMA [CanDel] TRANSFER操作，只保留存储过程的最近4个版本，同时用作业作为垃圾回收器，删除CanDel架构中留存超过3个月的对象。

但偶尔需要回滚，把存储过程从CanDel转移回原架构时，发现旧权限已丢失。我们使用的是SQL Server 2022，以下是复现代码：

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET NOCOUNT ON;
GO
USE tempdb;
GO
/* Cleanup */
IF OBJECT_ID('dbo.USP_PermissionsTest') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.USP_PermissionsTest;
END;
IF OBJECT_ID('CanDel.USP_PermissionsTest') IS NOT NULL
BEGIN
    DROP PROCEDURE CanDel.USP_PermissionsTest;
END;
GO
/* Create test role */
IF USER_ID('UDR_Test') IS NULL
    CREATE ROLE [UDR_Test];
GO
/* Create dummy stored procedure */
IF OBJECT_ID('dbo.USP_PermissionsTest') IS NULL
BEGIN
    EXEC('CREATE PROCEDURE dbo.USP_PermissionsTest AS');
END;
GO
/* grant permission to the stored procedure */
GRANT EXECUTE ON dbo.USP_PermissionsTest TO [UDR_Test];
GO
/* check permissions */
SELECT      USER_NAME(p.grantee_principal_id) AS principal_name,
            dp.principal_id,
            dp.type_desc                      AS principal_type_desc,
            p.class_desc,
            CASE p.class_desc
                WHEN 'DATABASE' THEN DB_NAME()
                WHEN 'SCHEMA' THEN SCHEMA_NAME(p.major_id)
                WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(p.major_id)
                ELSE NULL
            END                               AS object_name,
            p.permission_name,
            p.state_desc                      AS permission_state_desc
FROM        sys.database_permissions           p
            INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
            LEFT OUTER JOIN sys.all_objects    o ON p.major_id = o.object_id
WHERE       p.grantee_principal_id = USER_ID('UDR_Test')
            AND o.object_id = OBJECT_ID('dbo.USP_PermissionsTest')
ORDER BY    o.name;
GO
/* Create different schema to a destination */
IF SCHEMA_ID('CanDel') IS NULL
BEGIN
    EXEC('CREATE SCHEMA [CanDel];');
END
GO 
/* Transfer stored procedure to other schema */
ALTER SCHEMA [CanDel] TRANSFER dbo.USP_PermissionsTest;
GO 
/* check permissions */
SELECT      USER_NAME(p.grantee_principal_id) AS principal_name,
            dp.principal_id,
            dp.type_desc                      AS principal_type_desc,
            p.class_desc,
            CASE p.class_desc
                WHEN 'DATABASE' THEN DB_NAME()
                WHEN 'SCHEMA' THEN SCHEMA_NAME(p.major_id)
                WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(p.major_id)
                ELSE NULL
            END                               AS object_name,
            p.permission_name,
            p.state_desc                      AS permission_state_desc
FROM        sys.database_permissions           p
            INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
            LEFT OUTER JOIN sys.all_objects    o ON p.major_id = o.object_id
WHERE       p.grantee_principal_id = USER_ID('UDR_Test')
            AND o.object_id = OBJECT_ID('CanDel.USP_PermissionsTest')
ORDER BY    o.name;
GO
/* Cleanup */
IF OBJECT_ID('dbo.USP_PermissionsTest') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.USP_PermissionsTest;
END;
IF OBJECT_ID('CanDel.USP_PermissionsTest') IS NOT NULL
BEGIN
    DROP PROCEDURE CanDel.USP_PermissionsTest;
END;
GO
IF SCHEMA_ID('CanDel') IS NOT NULL
BEGIN
    EXEC('DROP SCHEMA [CanDel];');
END
GO 

这是SQL Server的设计特性，并非功能变更。

当使用ALTER SCHEMA ... TRANSFER转移对象时，对象的权限不会随对象一起迁移，原因如下：

• 对象级权限绑定到对象的「架构+名称」组合及对象ID，转移后原权限条目仍指向旧架构下的对象标识，新架构下的对象不会继承这些权限。
• 该行为从SQL Server早期版本（如2008）就已存在，并非SQL Server 2022的新变更。

若需保留权限，可参考以下方案：

• 转移前导出对象权限，转移完成后重新应用到新架构的对象上
• 优先使用架构级权限替代对象级权限，例如对dbo架构授予EXECUTE权限给目标角色，架构内所有存储过程会自动继承该权限
• 编写脚本，在对象转移后自动重新赋予必要的权限

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