Search code examples
sql-servercdc

Unable to enable Microsoft Change Data Capture (CDC) sp_cdc_enable_db

I am using SQL Server 2019 and trying to enable Microsoft CDC on the database.

If I create a new database and run EXEC sys.sp_cdc_enable_db i see "commands completed successfully and it shows its enabled from sys.databases

enter image description here

The problem:

I have an existing legacy database that i want to set cdc on, if i clone this and run command EXEC sys.sp_cdc_enable_db I get:

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.'.

I googled this and found this answer: EXEC sp_changedbowner 'sa'

This changes the owner from the same as cdc_test to sa: DB principal

Rerunning EXEC sys.sp_cdc_enable_db gives me a new error 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.'

Im at a loss here and have tried to change the db owner again but if i change it back to the previous one the error doesnt change and i still see the second error instead of the first.

My Db principals on the cdc_test db is: db principals cdc_test Whilst on the existing_db_cdc is after setting the db owner to sa: db principals existing_db_cdc

Other things i have tried:

  1. ALTER AUTHORIZATION ON DATABASE::[existing_db_cdc] to sa - When rerunning exec sys.sp_cdc_enable_db returns second error
  2. Compared servernames from sysservers to serverproperty('servername') these all match on both databases

Notes This database is on a different server to where it was originally created, its on my localhost, trying on the server it was on before had the same errors.

Not sure whats different about this cloned db or what else to try this stack overflow says they had it related to a db trigger but the answer has a deadlink and im not sure how to find that out.

These are the two dbo users on the two databases, i tried to alter the existing one with alter user dbo with LOGIN = dbo but got Cannot alter the user'dbo' dbo users

Solution

  • If you are still dealing with this issue, we've been experiencing the same.

    In our case, the solution was disabling any custom database trigger that was auditing changes on database objects (functions, tables, procedures, etc.).

    Maybe that's not your case, but hope it helps.