Extracting data from SQL Server linked server

Question

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database.

In SQL Server management studio, I can see the linked server. I can browse the database on the server. I can see all tables in database.

When I try and query a linked table

select * from openquery(somedatabase,'select top 4 * from test')

I get error:

The OLE DB provider "MSDASQL" for linked server "somedatabase" indicates that either the object has no columns or the current user does not have permissions on that object.

When I try to script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get same error.

Any help would be greatly appreciated.

I referred to following posts but it did not help.

http://www.sqlservercentral.com/Forums/Topic1036585-391-1.aspx

Error Querying MySQL server linked to SQL Server 2008

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server#gsc.tab=0

Thanks.

Answer 1

Check the link server setup and see HOW the users connect to the link server. This can be done by going to Server Objects in SSMS -> Linked Servers -> [Link server name] -> right click then select Properties -> security tab. It can be done without a security context, using the current login's security context, or it can be done using a specific login. Find out which account is being used and then make sure that account has access to the table on the linked server.