Replication and CDC- Change data Capture

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  July 20, 2011 at 12:32 pm

  #254684

  My database is a push subscriber in the replication process. Now I have to enable CDC to capture DML changes in my subscriber tables. I don't have access to publisher or ditributer to accomplish this in another way.

  Step 1:

  USE Mydatabase

  GO

  EXEC sys.sp_cdc_enable_db

  GO

  Step 2: Enable a Table Without Using a Gating Role

  USE mydatabase

  GO

  EXEC sys.sp_cdc_enable_table

  @source_schema = N'dbo',

  @source_name = N'MyTable',

  @role_name = N'MyRole',-- tried with Null and role too

  @supports_net_changes = 1

  GO

  Error:

  Msg 916, Level 14, State 1, Procedure sp_cdc_enable_table, Line 45

  The server principal "S-1-9-3-2376335028-1223578694-2108800438-1707743367." is not able to access the database "MyDatabase" under the current security context.

  When Googled and found that CDC and Replication can co-exist but did not found how to do that.

  Any inputs are welcome but don't tell me use trigger or other change tracking tips.

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  July 20, 2011 at 12:34 pm

  #1355946

  I have tell more that I verified CDC is enabled on database and cdc tables are created but not able to enable on the table. I hope its clear to understand..

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  July 20, 2011 at 12:52 pm

  #1355963

  You might see a work around here.

  -Roy

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  July 20, 2011 at 1:04 pm

  #1355980

  Thanks Roy but that din't helped in my case.

  The link says grant connect to login but I'm already sysadmin and the owner of the database.

  I have connect permissions.

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]

• September 28, 2023 at 1:50 pm

  This was removed by the editor as SPAM