Running the following query on a SQL Server capture instance:
DECLARE @from_lsn binary (10), @to_lsn binary (10)
SELECT @from_lsn = sys.fn_cdc_get_min_lsn ( 'dbo_ABC' );
select @from_lsn;
SELECT @to_lsn = sys.fn_cdc_get_max_lsn ();
select *
from cdc.fn_cdc_get_all_changes_dbo_ABC(@from_lsn, @to_lsn, 'all');
Results in a value of 0x00000000000000000000 being returned for the @from_lsn value and the result of the select * is an entirely null row:
| __$start_lsn | __$seqval | __$operation | __$update_mask | mycolumn |
|---|---|---|---|---|
| NULL | NULL | NULL | NULL | NULL |
N.B. This behaviour does not occur when an obviously non existent change capture table is queried:
select *
from cdc.fn_cdc_get_all_changes_dbo_blahblahblah(@from_lsn, @to_lsn, 'all');
SQL Error [208] [S0002]: Invalid object name 'cdc.fn_cdc_get_all_changes_dbo_blahblahblah'.
The DBA reports that they cannot replicate the issue using their own ID.
This occurs when the table has been set up for CDC with a role that the querying user does not have access to, or the role has been changed after CDC was set up. The user may retain access to run the CDC function fn_cdc_get_all_changes_... and even query the table in the cdc schema.
To check the role, run this query:
select
capture_instance,
role_name
from
cdc.change_tables ct
where
capture_instance = 'dbo_ABC'
order by
role_name;
And look for any discrepancies in role names which may point to the reason the capture instance cannot be queried.
In our case, the table had been re-enrolled for CDC in the UAT database and the role had been changed from SA_SQLUATRead_CDCRole to SA_SQLPrdRead_CDCRole inadvertently.
Re-enrolling the table for CDC with the correct role name resolved the issue and the user was able to query the table again. The DBA wasn’t able to replicate the issue because their privileges were elevated above that of the CDC-querying user.