Remove CDC user account from database which is enabled for CDC feature

Prabhat Singh 1 Reputation point
2022-02-08T19:07:13.307+00:00

What impact we can have on CDC feature, if we remove CDC user from database, as its have db_owner right and could be a security threat for an organization?

What other alternate ways to mitigate this risk ? can we remove cdc user and directly assign db_owner to cdc schema to make CDC feature to work as is?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,708 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2022-02-08T22:58:03.943+00:00

    I guess that if you remove the CDC_user, that CDC stops working, unless you add another user to take that role. Which would have to be db_owner.

    an we remove cdc user and directly assign db_owner to cdc schema

    You add users to roles (and db_owner is a role), not schemas.

    0 comments
  2. Seeya Xi-MSFT 16,436 Reputation points
    2022-02-09T06:19:20.747+00:00

    Hi @Prabhat Singh ,

    CDC is a schema that has the same name as the user CDC, just like dbo, guest.
    Agree with Erland that You add users to roles (and db_owner is a role), not schemas.
    Here are some tips to get it more clear:
    Server login: means a user who has permission to login to a server.
    Server role: means a fixed set of server users, of which there are nine by default.
    Database user: means a user who has permission to be able to operate a database.
    Database roles: refers to a fixed set of database roles with certain privileges.
    Database schema: means a container for database objects.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments