3

I'm attempting to create an MCVE for this recent question.

SQL Server is returning an error when attempting to update the system tables in master to indicate CDC is enabled for my test database.

Here's the code:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'CDC_test')
BEGIN
    ALTER DATABASE CDC_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CDC_test;
END
CREATE DATABASE CDC_test;
ALTER AUTHORIZATION ON DATABASE::CDC_test TO [sa];
GO
USE CDC_test;
GO
EXEC sys.sp_cdc_enable_db;

And the error:

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193 [Batch Start Line 11]
Could not update the metadata that indicates database CDC_test is enabled for Change Data Capture. The failure occurred when executing the command 'sp_addrolemember 'db_owner', 'cdc''. The error returned was 916: 'The server principal "sa" is not able to access the database "master" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.

Some checks I've performed from the same session:

SELECT [SQL Server Edition] = SERVERPROPERTY('Edition')
    , [Am I member of sysadmin] = IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME())
    , [Is sa Member of sysadmin] = IS_SRVROLEMEMBER('sysadmin', 'sa')
    , [sa is enabled?] = (SELECT ~ sp.is_disabled FROM sys.server_principals sp WHERE sp.name = 'sa');
╔═════════════════════════════╦═════════════════════════╦══════════════════════════╦════════════════╗
║     SQL Server Edition      ║ Am I member of sysadmin ║ Is sa Member of sysadmin ║ sa is enabled? ║
╠═════════════════════════════╬═════════════════════════╬══════════════════════════╬════════════════╣
║ Enterprise Edition (64-bit) ║                       1 ║                        1 ║              1 ║
╚═════════════════════════════╩═════════════════════════╩══════════════════════════╩════════════════╝

SQL Server Login Mode is set to Mixed Authentication:

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'LoginMode';
╔═══════════╦══════╗
║   Value   ║ Data ║
╠═══════════╬══════╣
║ LoginMode ║    2 ║
╚═══════════╩══════╝

I can see the code for sys.sp_cdc_enable_db, which looks like:

create procedure [sys].[sp_cdc_enable_db]
as
begin
    declare @retcode int
            ,@containment tinyint
            ,@db_name sysname

    -- Verify SQL Server edition
    -- CDC is restricted to Eval, Enterprise and Developer editions
    IF (sys.fn_MSrepl_editionid () not in (30,31))
    BEGIN
        DECLARE @edition sysname
        SELECT @edition = CONVERT(sysname, SERVERPROPERTY('Edition'))
        RAISERROR(22988, 16, -1, @edition)
        RETURN (1)
    END

    -- Verify caller is authorized to enable change data capture for the database 
    if (isnull(is_srvrolemember('sysadmin'),0) = 0) 
    begin
        raiserror(22902, 16, -1)
        return 1
    end

    /*
    ** Contained Database check (Replication is not yet supported on contained databases)
    ** If the current database is a contained database, then we error out.
    */
    SELECT @containment=containment FROM sys.databases WHERE
        database_id = db_id()
    if (@containment != 0)
    BEGIN
        set @db_name = db_name()
        RAISERROR(12839, 16, -1, @db_name)
        RETURN(1)
    END

    exec @retcode = sys.sp_cdc_enable_db_internal

    if (@@error <> 0) or (@retcode <> 0)
    begin
        return 1
    end

    return 0
end

However, I cannot find the code for sys.sp_cdc_enable_db_internal... I've looked in master and mssqlsystemresource for both system and non-system objects. I can run sp_cdc_enable_db_internal from the DAC, but it still fails with the same error.

  • I ran this on another instance, and it worked without error. This must be "too localized" for some reason. – Max Vernon May 10 '18 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.