SQL Server Portal

Change Data Capture (CDC) is one of the best features shipped with SQL Server 2008. We usually use CDC to record the change of the tables using DML operations for audit purpose. Today, I configured CDC for one of my clients. In order to configure the CDC, first of all, you need to enable it. However, once I tried enabling it but it gave me an error.

Let me explain the error and its solution in detail.

Message Number: 22830

Severity : 16

Error Message: Could not update the metadata that indicates database %s is enabled for Change Data Capture. The failure occurred when executing the command ‘%s’. The error returned was %d: ‘%s’. Use the action and error to determine the cause of the failure and resubmit the request.

Error Generation:
Let me enable the CDC to generate the error.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_enable_db
GO
--OUTPUT

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
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.

Ooopps…… I am unable to enable the CDC. What is the solution ? Let me explain the solution.

Resolution:
If you look at the error message, you will find two error numbers 22830 & 15517. In fact the actual error is 15517 and the reason behind this is, you do not have a valid log in for the database owner of the selected database. The solution I found is from Microsoft support site. Given below is the script.

USE AdventureWorks2012 -- Change the database name
GO
sp_changedbowner 'sa' --Change the valid login name
GO

Once you execute the above statement, you can easily enable the CDC and enjoy the benefit of CDC.

Conclusion:
To conclude, whenever you come across this error, you should change the database owner of the selected database to a valid SQL Server log in.

Read Full Post »