7

So there is a linked server. However, I am not currently able to view all databases associated with that linked server from within SSMS after expanding the catalog dropdown. It is my understanding that this may be a permissions issue. However, I am unsure as to which permissions I need to grant in order to interact with tat db from the linked server.

Any ideas?

After executing the following command from the linked server:

select * from [<server>].[<database>].[dbo].table1

I receive the following error message:

The OLE DB provider "SQLNCLI10" for linked server "" does not contain the table """."dbo"."table1"". The table either does not exist or the current user does not have permissions on that table.

TLDR;

Problem: I'm not sure how to get one of the databases associated with my linked server to show up.!

Improve this question
9
  • "Not able to view" is difficult for others to troubleshoot. If you say SELECT * FROM linked_server.database.dbo.tablename for a table you know exists, what happens? If you get an error message, please state it explicitly (a lot of people like to say "SQL Server doesn't like it" for some reason).
    – Aaron Bertrand
    Commented Jan 21, 2014 at 19:30
  • @AaronBertrand gotcha. Also, I feel like this is a v similar issue to the question you responded to here: stackoverflow.com/questions/11975063/…. Do you by any chance know how he granted the user access to the db he was trying to 'add'?
    – Drew
    Commented Jan 21, 2014 at 19:37
  • Probably GRANT CONNECT ON DATABASE::dbname TO [login_name]; where login_name is whoever the linked server is impersonating - of course it probably has to be run on the other server, not via the linked server. And, of course, that won't magically grant that login any permissions on any objects within the database.
    – Aaron Bertrand
    Commented Jan 21, 2014 at 19:47
  • @AaronBertrand gotcha. Just tried that, but not avail. Also, just added a screenshot--hopefully that might help to clarify?
    – Drew
    Commented Jan 21, 2014 at 20:01
  • 1
    Steps that led me to a solution can be found here: stackoverflow.com/questions/21261853/… !
    – Drew
    Commented Jan 22, 2014 at 16:13

4 Answers 4

Reset to default
7

This is due to the account used for Linked Servers not being added to the security of the database trying to be accessed.

On the databases that are shown in the Linked Servers container, you will also find the account (back on the original db server) added to the Security section with Read access. Add that account to the newly created db (catalog in this case) on the original server and then refresh the linked servers on the target db server. Your catalog will now show.

Improve this answer
2

However, I am unsure as to which permissions I need to grant in order to interact with tat db from the linked server.

Assuming you are certain the target table exists, then look at the linked server. Either it uses one set of permissions for everyone, or there are individual logins assigned. In both cases, whatever login you're logged into that has the linked server is trying to access the remote server either with impersonation, or with a username hardcoded in the linked server logins list.

It's that user, the one in the linked server logins list, that needs to exist on the remote server, and have permissions on the remote server to your object.

P.S. for better performance, try to use EXECUTE AT or OPENQUERY instead of pure four part naming, particularly once you get into JOINing linked server tables.

Improve this answer
0

https://stackoverflow.com/questions/11975063/add-catalog-to-an-existing-linked-server/11975274#comment32035221_11975274

Solution: You can do this by sending dynamic SQL via the linked server:

EXEC Linkedservername.master..sp_executesql N'CREATE DATABASE databasename;

Of course this requires that you have the permissions to do so, and it's a simplistic command assuming that the default settings are fine - you may want to customize the CREATE DATABASE command.

Improve this answer
0

When I copied .dbf along with .fpt, my problem got resolved. Hope this helps. The table might have some dependencies, which you need to copy onto local machine. When I had copied only .dbf file i was getting same error as above.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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