I am creating an SSIS package using Change Data Capture. I have created the package, but I get the error below while executing it:
[CDC Control Task] Error: "Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.".
The error says sysadmin/db_owner role is required. However, is this really required or I am doing something wrong?
I depends on what you are trying to do in SSIS..Here is what MSDN lists out. and what privilege you need to have..
Access Authorization to Change Data:
Trickle-feed update packages need access to SQL Server 2016 CDC functions. Such access is granted, by default, to members of the db_owner fixed database role. Because the db_owner is a powerful role, when defining capture instances within SQL Server 2016 it is recommended to associate a gating security role to each capture instance that allows the SSIS CDC package to use a much more restricted user for processing the changes.
Access to CDC Database Current LSN:
The CDC Control task operations for marking the start LSN for change processing must be able to find the CDC Database current LSN. This is done using the procedure sp_replincrementlsn from the master database. Execute permission on this procedure must be given to the login used for connecting to the SQL Server 2016 CDC database.
Access to CDC States Table:
The CDC States table is used for automatically persisting CDC States that need to be updatable by the login used for connecting to the SQL Server 2016 CDC database. As this table is created by the SSIS developer, set the SQL Server 2016 system administrator as a user who is authorized to create SQL Server 2016 databases and perform administrative and maintenance tasks. In addition, a SQL Server 2016 system administrator who works with CDC enabled databases must be knowledgeable about SQL Server 2016 CDC technology and implementation.
