Security Model

Configuration and Administration

To either enable or disable change data capture for a database, the caller of sys.sp_cdc_enable_db (Transact-SQL) or sys.sp_cdc_disable_db (Transact-SQL) must be a member of the fixed server sysadmin role. Enabling and disabling change data capture at the table level requires the caller of sys.sp_cdc_enable_table (Transact-SQL) and sys.sp_cdc_disable_table (Transact-SQL) to either be a member of the sysadmin role or a member of the database db_owner role.

To support the security model, a special change data capture user and change data capture schema that is owned by the change data capture database user are created in the database when change data capture is enabled. All change data capture objects that are not in the resource database are created in this schema, and owned by the change data capture user. This includes any gating roles that are created when a table is enabled for change data capture.

Use of the stored procedures to support the administration of change data capture jobs is restricted to members of the server sysadmin role and members of the db_owner role.

Change Enumeration and Metadata Queries

To gain access to the change data that is associated with a capture instance, the user must be granted select access to all the captured columns of the associated source table. In addition, if a gating role is specified when the capture instance is created, the caller must also be a member of the specified gating role. Other general change data capture functions for accessing metadata will be accessible to all database users through the public role, although access to the returned metadata will also typically be gated by using select access to the underlying source tables, and by membership in any defined gating roles.

DDL Operations to Change Data Capture Enabled Source Tables

When a table is enabled for change data capture, DDL operations can only be applied to the table by a member of the fixed server role sysadmin, a member of the database role db_owner, or a member of the database role db_ddladmin. Users who have explicit grants to perform DDL operations on the table will receive error 22914 if they try these operations.