SQL: Błąd podczas włączania CDC: ‚The server principal … is not able to access the database „msdb” under the current security context.

28-Lut-2015

Konfiguruję CDC. Kolejne kroki to:

1. włączenie CDC dla bazy danych

USE AdventureWorks2014
EXEC sys.sp_cdc_enable_db

2. Dodanie określonej tabeli do CDC

EXEC sys.sp_cdc_enable_table @source_schema = ‚HumanResources’,
@source_name = ‚Shift’,
@role_name = NULL,
@supports_net_changes = 1

Niestety na tym etapie otrzymuję błąd:

Could not update the metadata that indicates table [HumanResources].[Shift] 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 „……” 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.

Zgodnie z sugestią na http://blogs.msdn.com/b/ramoji/archive/2010/05/13/error-while-enabling-cdc-on-a-table-the-server-principal-xyz-is-not-able-to-access-the-database-msdb-under-the-current-security-context.aspx

uruchomiłem sys.sp_cdc_add_job ręcznie, co rozwiązało problem:

EXEC [sys].[sp_cdc_add_job] @job_type = N’capture’

Dodaj komentarz:

Autor: Rafał Kraik