如何在SQL Server中克隆含权限的存储过程至新名称？

嘿，这个需求很实用！可惜SQL Server并没有提供像你示例里create procedure y as clone x这种一步到位的克隆语法，但咱们可以通过几个简单的步骤，把原存储过程的逻辑结构+权限完整复制到新名称上，下面给你详细拆解：

步骤1：复制存储过程的逻辑结构 #

首先得拿到原存储过程x的创建脚本，你可以用OBJECT_DEFINITION函数直接获取：

SELECT OBJECT_DEFINITION(OBJECT_ID('x')) AS OriginalProcScript;

执行这条语句后，会返回x的完整创建代码——你只需要把里面的存储过程名从x改成y，然后执行修改后的脚本，这样新的存储过程y就和x拥有完全一样的逻辑了。

举个实际例子，如果原过程x的定义是：

CREATE PROCEDURE x
AS
BEGIN
    SELECT UserID, UserName FROM dbo.Users;
END;
GO

那你修改后的脚本就是：

CREATE PROCEDURE y
AS
BEGIN
    SELECT UserID, UserName FROM dbo.Users;
END;
GO

执行它就创建了结构一致的y。

步骤2：复制原过程的权限 #

接下来要把x上的所有权限同步到y上，咱们可以通过系统视图生成对应的权限授予语句：

SELECT 
    'GRANT ' + dp.permission_name + ' ON OBJECT::y TO [' + dpgr.name + '];' AS PermissionGrantScript
FROM 
    sys.database_permissions dp
JOIN 
    sys.database_principals dpgr ON dp.grantee_principal_id = dpgr.principal_id
WHERE 
    dp.major_id = OBJECT_ID('x') 
    AND dp.class = 1; -- class=1代表对象级权限

执行这条查询后，会输出一系列GRANT语句，比如如果原过程给TestUser开了执行权限，就会得到：

GRANT EXECUTE ON OBJECT::y TO [TestUser];

把这些生成的语句全部执行一遍，y就拥有和x完全相同的权限了。

进阶：自动化克隆（写个工具存储过程） #

如果经常需要做这种操作，不如把上面的步骤封装成一个工具存储过程，一键完成克隆：

CREATE PROCEDURE dbo.CloneProcWithPermissions
    @SourceProcName NVARCHAR(128),
    @TargetProcName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 生成并执行新存储过程的创建脚本
    DECLARE @ProcDefinition NVARCHAR(MAX);
    SELECT @ProcDefinition = REPLACE(OBJECT_DEFINITION(OBJECT_ID(@SourceProcName)), @SourceProcName, @TargetProcName);
    
    IF @ProcDefinition IS NOT NULL
        EXEC sp_executesql @ProcDefinition;

    -- 2. 生成并执行权限同步脚本
    DECLARE @PermissionScripts NVARCHAR(MAX);
    SELECT @PermissionScripts = STRING_AGG(
        'GRANT ' + dp.permission_name + ' ON OBJECT::' + @TargetProcName + ' TO [' + dpgr.name + '];',
        CHAR(13) + CHAR(10) -- 换行分隔每条语句
    )
    FROM sys.database_permissions dp
    JOIN sys.database_principals dpgr ON dp.grantee_principal_id = dpgr.principal_id
    WHERE dp.major_id = OBJECT_ID(@SourceProcName) AND dp.class = 1;

    IF @PermissionScripts IS NOT NULL
        EXEC sp_executesql @PermissionScripts;

    PRINT '存储过程 ' + @TargetProcName + ' 已成功克隆（含权限）！';
END;
GO

使用的时候只需要调用：

EXEC dbo.CloneProcWithPermissions 'x', 'y';

就能自动完成所有操作，非常方便。

注意事项 #

• 如果原存储过程依赖其他对象（比如表、视图、其他存储过程），新的y会自动继承这些依赖，但要确保执行者对这些依赖对象也有对应的访问权限。
• 跨数据库克隆的话，记得在对象名前加上数据库和架构的完整路径（比如[DBName].[Schema].[ProcName]），避免上下文错误。

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