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.

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
add comment

2 Answers 2

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
add comment

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
add comment

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.