Post reply

Issue with synonyms and linked servers

  • March 20, 2019 at 8:24 am

    #371757

    Hi all

    I've got a bit of an issue with a synonym being access via a linked serever.

    On our development server I have a line of SQL that reads as follows (this is just for test purposes):-
    SELECT TOP 100 * FROM [DevelopmentServer].DocumentsStaging.dbo.tbl_MT_Documents

    I've confirmed that the linked server works but, when I access it from another server (which is the intention), I get as far as (from a typing perspective):-
    [DevelopmentServer].DocumentsStaging.dbo.
    but the synonyms don't appear.

    I can see the rest of the tables.

    I've checked and the synonym is correctly set up.

    Is this just an oddity of synonyms that they can't be used across linked servers?

  • March 20, 2019 at 8:29 am

    #2025018

    i think this is an intelli Sense question, actually, where you expect the synonyms you are looking for to appear for ease of use, right?
    the synonyms exist, but don't auto-populate as you type?
    i think the driver might not return synonyms as well as other objects, even if they exist.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 20, 2019 at 8:41 am

    #2025022

    Hi Lowell

    Thanks for that.
    That does (vaguely) make sense).

    Now that's been sorted (and I know I've got to type the whole thing out), we come to a second issue.
    When I try that same code I posted above:-
    SELECT TOP 100 * FROM [DevelopmentServer].DocumentsStaging.dbo.tbl_MT_Documents
    I get the following error message:-

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object ""DocumentsStaging"."dbo"."tbl_MT_Documents"". The OLE DB provider "SQLNCLI11" for linked server "DevelopmentServer" indicates that either the object has no columns or the current user does not have permissions on that object.

    If I'm not going across a linked server, that piece of code works quite happily from any database on the same server and gives me the results I expect.

    Any ideas?

  • March 20, 2019 at 9:28 am

    #2025029

    yes, your permissions error is related to how you set up the linked server.
    you typically have a few choices, i usually use one of the bottom two from this screenshot, where i either pass the credentials of the current user, or use SQL account that i create that has permissions. don't put sa or superuser of some kind in there, create a user for the linked server with minimal db_reader permissions.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 20, 2019 at 9:59 am

    #2025035

    Hi Lowell

    I've just checked and I'm using the same setup as in your screenshot for the linked server.
    The user I'm using does have access to that database.

    I'm guessing this is just an oddity. unless you have any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply