Missing DB on linked server

Question

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

• Aaron Bertrand: "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).
• Drew: @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'?
• Aaron Bertrand: 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.
• Drew: @AaronBertrand gotcha. Just tried that, but not avail. Also, just added a screenshot--hopefully that might help to clarify?
• Drew: Steps that led me to a solution can be found here: stackoverflow.com/questions/21261853/… !

Answer 1

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.

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.

Answer 3

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.

Answer 4

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.