Unable to Enable CDC in SQL Server 2019

Question

Encountering the following error message while attempting to enable Change Data Capture (CDC) on a SQL Server 2019 database:

Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 258 [Batch Start Line 8] 
Could not update the metadata that indicates database eeterpdb 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.'. 
Use the action and error to determine the cause of the failure and resubmit the request. 
Completion time: 2025-03-20T04:52:54.0837141-05:00

The attempts made so far include:

• Verifying user access and confirming that the user has ownership of the database.
• Attempting to assign the owner role to another user, but encountered the same error.
• Researching articles related to this issue, which suggested using the 'sa' (System Administrator) account as a last resort.

Despite these efforts, the client continues to experience the same issue.

Answer 1

The error 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 occurs typically occurs when a database has been restored to a different server than the original one. This often results in a mismatch between the owner as recorded in sys.databases and the owner as recorded inside the database.

You can run this statement:

SELECT owner_sid FROM sys.databases WHERE name = 'YourDB'
UNION
SELECT sid FROM YourDB.sys.database_principals WHERE name = 'dbo'

It should only return one row, but it may return two rows if you have this mismatch.

The remedy is to set the owner of the database. It does not have to be sa. As a matter fact, I don't think it is best practice to have sa own database. A database should be owned by an SQL Login that exists only to own that databases, and which has been disabled, and which has been granted no permissions what so ever.

ALTER AUTHORIZATION ON DATABASE::YourDB TO YourDB$owner

Answer 2

we found that CDC was enabled for your database, and the issue occurred because the principal_id was set to NULL, causing the sp_cdc_enable_db_internal procedure to execute under the default security context.

To address this, we verified the database owner (sa) and checked the database principal. Upon finding it set to NULL, we reassigned the owner and re-executed the CDC process.

Please verify and confirm whether CDC has been enabled for the other tables. We are awaiting your response to proceed further.