SQL Server 2019启用Microsoft CDC失败的问题求助

问题概述 #

新创建的数据库可正常启用Microsoft CDC，但克隆的遗留数据库执行EXEC sys.sp_cdc_enable_db时先后触发两个错误：

1. 初始错误：

Could not update the metadata that indicates database existing_db_cdc is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

2. 将库所有者改为sa后，出现新错误：

Could not update the metadata that indicates database existing_db_cdc is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_objects'. The error returned was 2759: 'CREATE SCHEMA failed due to previous errors.'

解决步骤 #

1. 修复dbo用户与登录名的关联 #

克隆数据库常出现dbo用户与登录名关联失效的情况，执行以下操作：

• 验证dbo用户与sa登录名的SID是否一致：

  USE [existing_db_cdc];
  SELECT name, sid FROM sys.database_principals WHERE name = 'dbo';
  SELECT name, sid FROM sys.server_principals WHERE name = 'sa';
  

• 若SID不匹配，重新将数据库所有者设置为sa：

  USE [existing_db_cdc];
  EXEC sp_changedbowner 'sa';
  

2. 清理CDC残留对象 #

克隆过程可能遗留未完成的CDC对象，导致Schema创建失败，执行以下清理脚本（操作前请备份数据库）：

USE [existing_db_cdc];

-- 删除CDC相关SQL Server Agent作业
DECLARE @job_id UNIQUEIDENTIFIER;
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name LIKE 'CDC%existing_db_cdc%';
IF @job_id IS NOT NULL
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_id = @job_id;
END

-- 删除cdc架构及下属对象
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'cdc')
BEGIN
    DECLARE @cleanup_sql NVARCHAR(MAX) = '';
    
    -- 生成删除CDC存储过程的语句
    SELECT @cleanup_sql += 'DROP PROCEDURE ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ';' + CHAR(13)
    FROM sys.procedures p
    JOIN sys.schemas s ON p.schema_id = s.schema_id
    WHERE s.name = 'cdc';
    
    -- 生成删除CDC表的语句
    SELECT @cleanup_sql += 'DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';' + CHAR(13)
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name = 'cdc';
    
    -- 删除cdc架构
    SELECT @cleanup_sql += 'DROP SCHEMA cdc;' + CHAR(13);
    
    EXEC sp_executesql @cleanup_sql;
END

-- 重置数据库CDC启用状态
UPDATE sys.databases SET is_cdc_enabled = 0 WHERE name = 'existing_db_cdc';

3. 禁用数据库触发器 #

部分数据库级触发器会干扰CDC对象创建，临时禁用后再尝试启用CDC：

USE [existing_db_cdc];
-- 查看所有数据库触发器状态
SELECT name, is_disabled FROM sys.triggers WHERE parent_class_desc = 'DATABASE';
-- 禁用数据库级触发器
DISABLE TRIGGER ALL ON DATABASE;

4. 重新启用CDC #

完成上述步骤后，执行启用命令：

USE [existing_db_cdc];
EXEC sys.sp_cdc_enable_db;

5. 验证启用结果 #

执行以下语句确认CDC状态：

SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'existing_db_cdc';
SELECT * FROM sys.schemas WHERE name = 'cdc';

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