Unable to query SQL server metadata through linked server

Question

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.

Answer 1

@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.

Answer 2

The issue is not security related :-).

In your query, you are selecting from a table on a remote system, but the WHERE condition is using the OBJECT_ID() function, which can only see the local instance, and is not running in the scope of the Linked Server connection.

Why don't you try:

select *
from [DBTEST].[AdventureWorks].sys.objects
where [name] = N'DatabaseLog';

And that query makes more sense anyway since you already had the name, so no sense in querying to get the ID from the name just to use the ID in a second query ;-).

If you want / need to also filter on the Schema name, then you will have to add the JOIN to the [DBTEST].[AdventureWorks].sys.schemas table as OBJECT_SCHEMA_NAME is also local scope only.