Link to home
Start Free TrialLog in
Avatar of Aiysha
AiyshaFlag for United States of America

asked on

Limit data changes in linked table.

We have sql server with database and tables in it. If I can connect to it via odbc, I can link to the tables in access database. After the linking I am easily able to make changes, add and delete the data in the tables. Can anyone suggest a lock system (user verification) that will enable user to link to the tables but not make any changes to the data in the table. My DBA can do it but I wanted to know opinion from the experts to propose a solution.

Thank you.
Avatar of PatHartman
PatHartman
Flag of United States of America image

There is no build in method with Access.  You can institute a login form and write code yourself to manage who can do what but it is far better to leave it to the database engine.
Since you have a SQL BE you can have SQL logins with different levels of permissions.
Start with an article like this:Creating a user and granting table level permissions in SQL Server
"If I can connect to it via odbc, I can link to the tables in access database. After the linking I am easily able to make changes, add and delete the data in the tables." - this obviously means that the login used in that ODBC has FULL permissions like db_owner or even worse sysadmin that that particular SQL Server database.
The easiest way I see would be to add a new "public" SQL login called something like "sql_reader" or whatever you want to call it, give ONLY "db_datareader" access on that particular SQL login to the database(es) on that SQL and used that one in the ODBC configuration instead of your own which has elevated permissions in SQL for whatever reason.
User generated image
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.