Compartilhar via

Error while enabling CDC on a table. The server principal "xyz" is not able to access the database "msdb" under the current security context.

  • Artigo

We came across the following error after successfully enabling CDC on a Database and then trying to enable CDC on a table in SQL Server 2008 SP1 (Version 10.0.2531.0)

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607

Could not update the metadata that indicates table [dbo].[MyTable] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 916: 'The server principal "S-1-9-3-3045654011-13150378417-261222020-2009464159." is not able to access the database "msdb" under the current security context.' . Use the action and error to determine the cause of the failure and resubmit the request.

Since the failure message is saying the error occured while creating the Capture job, we tried explicitly creating the same using the system procedure "sp_cdc_add_job" as shown below and it solved the issue for us.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

USE

MyDatabase

GO

--Enable CDC on the database

EXECUTE

sys.sp_cdc_enable_db

go

--Manually create Capture job to avoid the job creation error

EXEC

[sys].[sp_cdc_add_job] @job_type = N'capture'

go

--Enable CDC on the tables

EXECUTE

sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = 'AdjustmentReason', @role_name = N'cdc_admin', @supports_net_changes = 1, @index_name = null, @captured_column_list = null, @filegroup_name = N'PRIMARY'

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If you want more details on the above error, you can refer to the link https://msdn.microsoft.com/en-us/library/ee342155.aspx.

Please drop me an encouraging note if this work around has helped in solving your error. Thanks in advance.

Comments

  • Anonymous
    July 11, 2014
    Thanks a millions for this article as it really helped me. Keep up the good work

  • Anonymous
    August 06, 2014
    msdn.microsoft.com/.../cc645937(v=sql.105).aspx Change data capture cannot function properly when the Database Engine service or the SQL Server Agent service is running under the NETWORK SERVICE account. This can result in error 22832.