SQL Server：如何通过存储过程向带IDENTITY的列插入并解决权限报错？

1. 修正SET IDENTITY_INSERT的执行方式 #

SET IDENTITY_INSERT是会话级设置，必须在目标服务器的会话中执行，本地直接运行SET IDENTITY_INSERT [目标服务器].[库].[dbo].[Client] ON无效。要通过动态SQL在目标端执行：

-- 开启IDENTITY_INSERT
EXEC [目标服务器].[目标库].[sys].[sp_executesql] N'SET IDENTITY_INSERT [dbo].[Client] ON;'
-- 插入完成后关闭
EXEC [目标服务器].[目标库].[sys].[sp_executesql] N'SET IDENTITY_INSERT [dbo].[Client] OFF;'

2. 补充ALTER表权限 #

SET IDENTITY_INSERT需要ALTER表的权限，仅给INSERT权限不够。给链接服务器使用的登录账户授权：

GRANT ALTER ON [dbo].[Client] TO [链接服务器登录账户];

如果用本地账户模拟执行，要确保该账户在目标服务器有映射且具备ALTER权限。

3. 强制显式指定列名插入 #

绝对不能用INSERT INTO ... SELECT *，必须显式列出所有列（包括IDENTITY列），示例：

INSERT INTO [目标服务器].[目标库].[dbo].[Client]
(ID, 列名1, 列名2) -- 必须包含IDENTITY列ID
SELECT ID, 列名1, 列名2 FROM [源库].[dbo].[Client]
WHERE ID NOT IN (SELECT ID FROM [目标服务器].[目标库].[dbo].[Client])

4. 检查链接服务器RPC Out配置 #

在SSMS中右键链接服务器→属性→服务器选项，把RPC Out设为True，否则无法通过sp_executesql向目标端传递会话设置命令。

整合后的存储过程示例 #

CREATE PROCEDURE dbo.SyncClientTable
AS
BEGIN
    SET NOCOUNT ON;

    -- 开启目标表的IDENTITY_INSERT
    EXEC [目标服务器].[目标库].[sys].[sp_executesql] N'SET IDENTITY_INSERT [dbo].[Client] ON;';

    BEGIN TRY
        -- 同步数据（显式列名）
        INSERT INTO [目标服务器].[目标库].[dbo].[Client]
        (ID, ClientName, ContactPhone)
        SELECT ID, ClientName, ContactPhone
        FROM [源服务器].[源库].[dbo].[Client]
        WHERE ID NOT IN (SELECT ID FROM [目标服务器].[目标库].[dbo].[Client]);

        -- 关闭IDENTITY_INSERT
        EXEC [目标服务器].[目标库].[sys].[sp_executesql] N'SET IDENTITY_INSERT [dbo].[Client] OFF;';
    END TRY
    BEGIN CATCH
        -- 异常时强制关闭IDENTITY_INSERT，避免锁表
        EXEC [目标服务器].[目标库].[sys].[sp_executesql] N'SET IDENTITY_INSERT [dbo].[Client] OFF;';
        THROW;
    END CATCH
END

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