I have two servers, ServA and ServB. ServA is set up as a linked server from ServB. The connection tests OK.
On ServA, I have a database DbA that is in the catalog for the linked server on ServB. Only a subset of tables show in the catalog, for examplet TblA is in catalog, TblB is not in the catalog. There is no difference in permissions between the two tables.
From a ServB connection, including on the server, I am able to query ServA.DbA.dbo.TblA, but I get the error:
The OLE DB provider "MSOLEDBSQL" for linked server "ServA" does not contain the table ""DbA"."dbo"."TblA"". The table either does not exist or the current user does not have permissions on that table.
This started after a recent upgrade from SQL2017 to SQL2019, which included a move to new servers.
I have verified that this behavior is consistent to ANY table that is in DbA, those in the catalog can be queried, those not in the catalog get this error.
Does anyone have an idea how to correct this? Again, there are no special permissions at the table level, and I have tested giving the User for the linked server sys_admin privileges, and that has not made a difference. I can find no way to add a table to the catalog.