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?
0
1
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.
0
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.
-
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
