SQL Server 2008 R2: Error returned was 15517 (CDC)

Ok!!!!

Here I come.

ERROR 15517

To fix this error, first you need to check who is "DBOwner" of that database.

This error is thrown when you are trying to enable CDC feature on a particular database which has "DBOwner" value of NULL.

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.

Let's take "AdventureWorks" as our sample database.

Run following script in SSMS


SELECT name
        ,database_id
        ,USER_NAME(owner_sid) as DBOwner
FROM sys.databases WHERE name ='AdventureWorks'


IF you see 'DBOwner' as NULL value, change this to 'sa'.

To do this run following script

Use AdventureWorks
GO
Exec sp_changedbowner 'sa'
GO


Even we are saying to change it to 'sa' which is not our loginID or userAccount, what this does is that it assign the "DBOwner" to "dbo" over which we can implement CDC.

Now you are all set to get rid of this error.


Note: More to come...