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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 table. So my question is how to build a linked server (from SQL Server) connected only to one specific table in Access?

share|improve this question

Setting up an Access DB as a linked server gets pretty ugly, but if you absolutely have to do it, you can split that one table into a separate database file, and create a linked table pointing to it from the original Access DB file. Then set up a linked server in SQL Server pointing to the Access database file with that one table.

Better option: Build an SSIS package that periodically imports/synchronizes data from your Access database.

Best option: Use the upsizing wizard in Access to migrate the data to SQL Server, and continue using Access as the front-end application, but not as the storage engine.

share|improve this answer

You could create a view only showing the desired table. (Or perhaps a stored procedure). Only give the user(s) read rights to the view. Lock down the linked server.

share|improve this answer
I am unsure that is enough, depending on how locked down it is supposed to be. A user with rights to the linked server can circumvent views and stored procedures and go directly through the linked server to select whatever he has rights to see. – RLF Sep 13 '13 at 15:40

Your Answer


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.