DBA

Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal cannot be enabled for Change Data Capture because a database user named cdc

Advertisements

Overview:

There was a database restore from production to UAT and the restore was done without Keep CDC option. When developer asked to enable CDC the following was the error.

Always keep CDC while restore is best option whether we have CDC or not.

use [DB Name]
go
exec sys.sp_cdc_enable_db

Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 49 [batch start line 2]

The database ‘DBName’ cannot be enabled for Change Data Capture because a database user named ‘cdc’ or a schema named ‘cdc’ already exists in the current database. These objects are required exclusively by Change Data Capture. Drop or rename the user or schema and retry the operation.

Solution: Drop all CDC objects and enable CDC again.

use [T]
go
exec sys.sp_cdc_enable_db

-- Remove tables
SELECT  'drop table cdc.' + QUOTENAME(t.name) AS name
    FROM    sys.tables t
            JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE   s.name = 'cdc'

-- Remove SP
SELECT  'drop procedure cdc.' + QUOTENAME(pr.name) AS name
    FROM    sys.procedures pr
            JOIN sys.schemas s ON pr.schema_id = s.schema_id
    WHERE   s.name = 'cdc'

-- Remove functions
SELECT  'drop function cdc.' + QUOTENAME(fn.name) AS name
    FROM    sys.objects fn
            JOIN sys.schemas s ON fn.schema_id = s.schema_id
    WHERE   fn.type IN ( 'FN', 'IF', 'TF' )
            AND s.name = 'cdc'

-- Change AUTHORIZATION  schema role

SELECT   'ALTER AUTHORIZATION ON ROLE::'+DP1.name+' to DBO' , DP1.name
FROM    sys.database_principals AS DP1
        JOIN sys.database_principals AS DP2 ON DP1.owning_principal_id = DP2.principal_id
WHERE   DP1.type = 'R'
        AND DP2.name = 'cdc';

-- Remove schema and user
go 
DROP SCHEMA [cdc]
GO
DROP USER [cdc]
GO

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: