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
