I have a scenario where a user wants to do the following through a linked server:
SELECT *
FROM [DBTEST].[AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog');
The linked server is configured to use Be made using this security context
and the linked server login has read access on the [DBTEST].[AdventureWorks]
database.
The above query does not return any rows when executed through the linked server. However, when I login directly to DBTEST
using the linked server login and do the following, it does return a row:
SELECT *
FROM [AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog')
I am confused why it is not working through the linked server and what I need to do in order to fix it.
One more strange thing is even if I grant sa
rights to the linked server login it still does not return any rows when executed through the linked server.
Both servers are SQL Server 2008 Enterprise Edition.