Can I change the owner of the cdc schema?

Question

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.

Answer 1

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_<capture_instance> and/or cdc.fn_cdc_get_net_changes_<capture_instance>. 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).

Answer 2

Alternate Solution?

You asked:

Because I want to leverage ownership chaining and read from this schema using a proc, can I change the owner to dbo?

Instead of changing the ownership, why not just grant yourself or others the privileges to read from the schema? You could go right up to CONTROL (but not ALTER):

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::cdc TO <desired_principal>;

The idea of ownership chaining is actually considered to be used with caution by Microsoft:

A user with ALTER permission on a schema can use ownership chaining to access securables in other schemas, including securables to which that user is explicitly denied access. This is because ownership chaining bypasses permissions checks on referenced objects when they are owned by the principal that owns the objects that refer to them. A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema's owner. When possible, you should avoid granting ALTER permission on a schema if the schema's owner also owns other schemas.

^{Reference: GRANT Schema Permissions (Transact-SQL) (Microsoft Learn)}

You would possibly bring yourself into this situation by changing the schema owner to dbo.