HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Can I change the owner of the cdc schema?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
ownercanthecdcchangeschema

Problem

I've enabled Change Data Capture (CDC) on a database using exec sys.sp_cdc_enable_db

This creates a new cdc schema which is owned by the cdc user
select
s.*
, dp.name
from sys.schemas as s
join sys.database_principals as dp
on dp.principal_id = s.principal_id


Because I want to leverage ownership chaining and read from this schema using a proc, can I change the owner to dbo?
ALTER AUTHORIZATION ON SCHEMA::cdc TO dbo


This command works and changes the owner. But the CDC has been enabled for a while, and I'm worried about breaking something further down the road.

Solution

From the documenation:

For Change data capture (CDC) to function properly, you shouldn't manually modify any CDC metadata such as CDC schema, change tables, CDC system stored procedures, default cdc user permissions (sys.database_principals) or rename cdc user.

Elsewhere in that same documentation (in the "Data Flow" section), there's a diagram that heavily implies that you should be using cdc.fn_cdc_get_all_changes_ and/or cdc.fn_cdc_get_net_changes_. In the documentation for those, there's this bit about permissions:

Requires membership in the sysadmin fixed server role or db_owner fixed database role. For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role.

Meaning that SELECT permission on the underlying table should be sufficient and you don't need to alter anything about how CDC works (permissions or otherwise).

Context

StackExchange Database Administrators Q#331020, answer score: 5

Revisions (0)

No revisions yet.