locked
Can't enable CDC through login account that has sysadmin rights RRS feed

  • Question

  • Hi there,

    I get an error when trying to enable CDC on one of our SQL2012 instances, when using a login account that is a member of the sysadmin fixed server role.

    Procedure sp_cdc_enable_db_internal, Could not update the metadata that indicates database <databasename> is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)' Could not obtain information about WindowsNT group/user" <domain\username>

    The login account in question created the database initially so is mentioned in the "Login name:" under the dbo user for this particular database.

    When I ran sp_changedbowner 'sa' on the database and then ran the command again to enable CDC it worked. Why is that?

    I thought that to enable CDC you either had to be a member of the 'db_owner' role in the actual database OR a member of the sysadmin role.

    Can some one help explain to me please?

    Thanks.

    Friday, February 1, 2013 11:57 AM

Answers

  • Your error not obtain information about WindowsNT group/user" <domain\username> implies that the SQL Server could not properly ask the domain controller who the login was, and what permissions they had. This could have been a transient problem because of network issues or a domain controller issue. Or it could be that the login was from an old domain and the computer was moved, or the domain trust relationship was changed, etc. Or the Windows token for the login might have gotten damaged some how, and it lost a Windows group membership.

    Books Online for sys.sp_cdc_enable_db says you need to be a member of the sysadmin fixed server role to enable CDC. So I don't think db_owner is enough.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Thursday, February 21, 2013 8:09 AM
    Friday, February 1, 2013 5:01 PM