locked
Stuck on CDC disable error RRS feed

  • Question

  • Hello!

    We are working on automating deployments for our SQL Server 2012 SP1 instance that uses CDC.  We are getting the following error when we try to run:

    exec sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name = N'SUser',
    @capture_instance = N'all'

    Error:

    Msg 22833, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 148
    Could not update the metadata that indicates table [dbo].[SUser] is not enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_disable_table_tranx'. The error returned was 22837: 'Could not delete table entries or drop objects associated with capture instance 'dbo_SUser'. The failure occurred when executing the command 'sp_cdc_drop_change_table_objects dbo_SUser'. The error returned was 14677: 'The user does not have permission to perform this operation. The user must be a member of data collector role 'mdw_admin'.'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.

    I am troubleshooting this as sysadmin running the query from SSMS.  I have scoured the forums and the googles, but I can't really find any info on this.  The error mentions that the user must be a member of data collector role, which we spelled out explicitly also just for the fun of it (didn't help....).

    Any ideas?

    Any help is much appreciated!

    Thanks!

    -Bill S.

    Monday, October 21, 2013 9:03 PM

Answers

  • OK today we have finally made a breakthrough!  Adding the cdc User in the database to the mdw_admin role that exists in our database does the trick!  We are not sure why the mdw_ roles exist in our database at this point (they do not exist in our prod or other environments), but it appears that adding a database to the management data warehouse and creating these roles conflicts with CDC.  To verify, I added the CDC user in the database to the mdw_admin role and everything worked fine!

    Any idea if we are ok to just delete the mdw_roles from our database?  At this point, we are not collecting any data on the database with Management Data Warehouse.

    Let me know.

    Thanks!

    -Bill S.

    • Marked as answer by Bill Schultz Thursday, March 26, 2015 12:25 PM
    Friday, December 13, 2013 7:28 PM

All replies

  • Hi Bill,

    Did you try to add the user to the database role "mdw_admin" explicitly?

    Users must be members of one or more of the fixed database roles provided for accessing the management data warehouse to perform some task: mdw_admin,mdw_writer,mdw_reader.
    By default, no user is a member of these database roles. User membership in these roles must be granted explicitly.Users who are members of the sysadmin fixed server role have full access to data collector views. However, they need to be explicitly added to database roles to perform other operations.

    Reference:http://technet.microsoft.com/en-us/library/bb630341.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Tuesday, October 22, 2013 1:36 AM
  • Yes, we tried that to cover our bases.  We got the same error as before.
    Tuesday, October 22, 2013 12:23 PM
  • OK today we have finally made a breakthrough!  Adding the cdc User in the database to the mdw_admin role that exists in our database does the trick!  We are not sure why the mdw_ roles exist in our database at this point (they do not exist in our prod or other environments), but it appears that adding a database to the management data warehouse and creating these roles conflicts with CDC.  To verify, I added the CDC user in the database to the mdw_admin role and everything worked fine!

    Any idea if we are ok to just delete the mdw_roles from our database?  At this point, we are not collecting any data on the database with Management Data Warehouse.

    Let me know.

    Thanks!

    -Bill S.

    • Marked as answer by Bill Schultz Thursday, March 26, 2015 12:25 PM
    Friday, December 13, 2013 7:28 PM