如何编写TSQL脚本实现带表值参数的存储过程创建/修改并保留GRANT权限

针对变更表值参数时只能删除重建存储过程、但会丢失DBA配置权限的问题，推荐以下更可靠的实现方案：

方案：通过系统视图导出权限，重建后自动恢复 #

核心逻辑是先从SQL Azure系统视图提取目标存储过程的所有GRANT权限，临时存储后执行删除-重建操作，最后自动恢复权限。相比全局临时表，本地临时表更安全（无跨会话冲突），且直接读取系统元数据的方式更准确。

完整脚本示例 #

-- 1. 临时保存当前存储过程的权限
IF OBJECT_ID('tempdb..#SavedPermissions') IS NOT NULL
    DROP TABLE #SavedPermissions

SELECT 
    dp.state_desc + ' ' + dp.permission_name + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' TO ' + QUOTENAME(dpgr.name) AS PermissionStatement
INTO #SavedPermissions
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals dpgr ON dp.grantee_principal_id = dpgr.principal_id
WHERE o.type = 'P' 
    AND o.name = 'YourProcName' -- 替换为目标存储过程名称
    AND s.name = 'dbo' -- 替换为存储过程所属架构

-- 2. 删除原有存储过程（若存在）
IF OBJECT_ID('dbo.YourProcName', 'P') IS NOT NULL
    DROP PROCEDURE dbo.YourProcName

-- 3. 创建新的存储过程（包含变更后的表值参数）
CREATE PROCEDURE dbo.YourProcName
    @UpdatedTVP YourModifiedTableType READONLY -- 替换为结构变更后的表值参数
AS
BEGIN
    SET NOCOUNT ON;
    -- 存储过程核心业务逻辑
    SELECT * FROM @UpdatedTVP
END

-- 4. 自动恢复之前保存的权限
DECLARE @PermissionCmd NVARCHAR(MAX)
DECLARE PermCursor CURSOR FOR SELECT PermissionStatement FROM #SavedPermissions
OPEN PermCursor
FETCH NEXT FROM PermCursor INTO @PermissionCmd
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @PermissionCmd
    FETCH NEXT FROM PermCursor INTO @PermissionCmd
END
CLOSE PermCursor
DEALLOCATE PermCursor

-- 清理临时表
DROP TABLE #SavedPermissions

方案优势 #

• 准确性高：直接读取系统元数据获取权限，避免手动维护权限语句的误差
• 安全性强：使用本地临时表（tempdb..#开头），不会和其他会话的临时表冲突
• 可复用性好：只需替换存储过程名、架构名和创建语句，即可快速适配其他存储过程

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