3

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.

2 Answers 2

3

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

3
  • Riiighht. I keep forgetting about this option :). Does it require any special option to be set to "true" on the Linked Server config? Such as RPC and/or RPC Out? Either way, +1 as this is also a good option. Sep 25, 2015 at 15:52
  • 1
    Yes, it depends on RPC Out but not RPC.
    – Hannah Vernon
    Sep 25, 2015 at 15:58
  • Thanks Max and srutzky for your help!! I have asked the user to use join solution of sys.objects and sys.schemas.
    – SQLPRODDBA
    Sep 29, 2015 at 3:24
3

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.

2
  • That makes complete sense. I didn't know that OBJECT_ID() function in where clause can't see linked server connection. Thanks for your help!
    – SQLPRODDBA
    Sep 25, 2015 at 16:01
  • @SQLPRODDBA No problem at all. If one of these answers worked for you, please do not forget to mark one of them as "accepted". And you are allowed to up-vote either one, or both, regardless of accepting, depending on what you felt was helpful. Sep 25, 2015 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.