Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 "<server>" does not
contain the table ""<database>"."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.!

share|improve this question
    
"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 Jan 21 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 Jan 21 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 Jan 21 at 19:47
    
@AaronBertrand gotcha. Just tried that, but not avail. Also, just added a screenshot--hopefully that might help to clarify? –  Drew Jan 21 at 20:01
    
That doesn't really help clarify, it's clear that the linked server cannot see that database (or the table inside of it). However, this could get into pretty intensive troubleshooting, since we have no idea how your linked server is set up, what security mechanism it is using, what login is actually being presented when you run this query, and what login you've granted these permissions to... –  Aaron Bertrand Jan 21 at 20:06

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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