0

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.

  • 2
    so, you don't have access to the table. Check the login used by the linked server to see if it has been GRANTed access. – Max Vernon May 13 '16 at 15:56
  • Yes. I am using admin login – Harleen May 27 '16 at 18:09
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.

  • Thanks for replying Eric. I tried using without a security context and administrator login. But I get same error. – Harleen Jun 2 '16 at 16:29
  • Do you have windows authentication set up for both servers or do you have the same login information for both? If you do, try using current security login context. One other thing I can think of is make sure the two servers can see each other so try pinging the server you are connecting to from the server you are trying to connect from. – Eric S Jun 3 '16 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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