Enable CDC fails with error Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal


Enable CDC fails with error Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal

Chris Skorlinski
SQL Server Escalation Services

Problem

While enabling Change Data Capture (CDC) on database you get following error:

EXECUTE sys.sp_cdc_enable_db

Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 198 [Batch Start Line 8]
Could not update the metadata that indicates database AdventureWorks2012 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.
Solution

You can encounter 22830 error following a database restore\attach when the user account did not exist in the database being restored.  For example, when restoring AdventureWorks while logged on as 'corp\chris' and enabling CDC, the user 'corp\chris' didn't exists in original backup of AdventureWorks.

You can change database owner to 'sa' or change to 'sa' then back to your user account.  This reset the security information allowing you to enable CDC.

sp_changedbowner 'sa'
GO

-- Optional, change back to account which restored database 
sp_changedbowner 'corp\chris'
GO
EXEC sys.sp_cdc_enable_db
GO

 


Comments (3)

  1. Nirmal Shah says:

    This error seems to be comming as a result of different problem source. In my case, I was having database which was having some db owner issue. After changing db owner to "SA" it just worked fine. BTW, I did have audit trigger but it didn't give me any trouble even if audit trigger(DDL trigger) was enabled. I hope this helps.

    Thanks

    Nirmal

  2. Marko says:

    Nirmal, your soultion worked in my case. Thank you.

  3. Srisubathra says:

    Yes same worked for me , thanks Nirmal

Skip to main content