SQL Server – Unable to Query Metadata Through Linked Server

linked-serversql serversql-server-2008

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.

Best Answer

@srutzky is correct - this is not security related. As he said, the OBJECT_ID() function is operating against the local SQL Server instance, not the remote instance.

If you want to execute the statement unchanged against the remote server, you'd need to run it through dynamic T-SQL, using the following:

DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SELECT *
FROM [AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N''dbo.DatabaseLog'');';

EXEC [DBTEST].AdventureWorks.sys.sp_executesql @sql;

This will execute the entire contents of @sql at the remote server.

Related Question