Error while configuring Change Data Capture (CDC) | SansSQL

Tuesday, February 15, 2011

Error while configuring Change Data Capture (CDC)

I was trying to configure Change Data Capture (CDC) on my system a while ago and I noticed that i did not had a test database on the newly installed Instance. So I backed up the AdventureWorks database from an existing instance on my laptop and restored on the new Instance.
Okay, Now after the restoration, I executed "Exec sys.sp_cdc_enable_db" command to enable CDC on the database but the database did not allow me to enable CDC by giving the below error.

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186
Could not update the metadata that indicates database CDC_AdventureWorks 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.
Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

This is because, the Database Owner was Invalid on the new Instance as the Login that was DBOwner in the other Instance was not present in the new Instance.
SELECT name
        ,database_id
        ,USER_NAME(owner_sid) as DBOwner
FROM sys.databases WHERE name ='CDC_AdventureWorks'

Fix
: To Fix this error, change the owner of the database to 'sa' or a valid Login.

Use CDC_AdventureWorks
GO
Exec sp_changedbowner 'sa'
GO
Once you change the database owner to 'sa' or a valid Login you should be now able to enable CDC on that Database.


Ads