dinsdag 17 januari 2012

Could not update the metadata that indicates database AdventureWorks2008R2 is enabled for Change Data Capture

Introduction
Currently investigating the CDC in SQL Server 2012 RC0 Denali and i ran into an error, which i want to discuss with you. The error happens when i want to turn on CDC. In this blogpost i'll present the error, a cause of the problem, a solution and conclusion.

The error
The error occurs when i want to execute the following statement:

EXEC sys.sp_cdc_enable_db

It returns this error:

Could not update the metadata that indicates database AdventureWorks2008R2 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.

Cause of the problem
I've downloaded the RC0 databases from the Microsoft site and restored them on my SQL Server 2012 RC0 Denali VM. Then, i executed this stored procedure: sys.sp_cdc_enable_db. In this article, http://support.microsoft.com/kb/913423, more information is available. This error message is shown when you are trying to enable CDC on a SQL Server 2008 R2 database for which the owner is not "sa". According to the article, this problem occurs when all the following conditions are true:
• You back up a database from an instance of SQL Server 2005 (e.g. SQLSErver 2008 R2 database). Then, you restore the database to an instance of SQL Server 2005 that is installed on another computer.
• The statement or the module is executed as the dbo (database owner) user.
• The owner of the database is a domain user or a SQL Server authorization login.


Solution
Execute the following statements:

USE AdventureWorks2008R2
GO

EXEC sp_changedbowner 'sa'

and the database owner changes to sa:


And the stored procedure sys.sp_cdc_enable_db executes successfully.

Conclusion
In this blogpost a problem is identified with databaseownership of the database and a solution is presented.

Greetz,
Hennie

2 opmerkingen: