October 7, 2011 at 2:16 pm
I have a database on a SQL 2008 server that has about 12 users that connect to it and use MS Access as a front end via ODBC and linking the tables.
Because of the data that is in this database, they only have access to a handful of views - they cannot change the data in this table - only import to it via a pass thru query.
However, if one of these users opened a new access db, and imported all of the tables, they would have full access to manipulate the data any way they wanted to. Is there a way to prevent this? To only expose via ODBC the tables / views that I want them to have access to - but anything else would not be exposed?
Thanks in advance
sb
October 7, 2011 at 2:35 pm
If they're using an ODBC connection to connect to the DB then and they will be completely limited to the SQL account being used in the ODBC connection. In SQL, grant read-only access to the tables you want that account to have access to, and it will be reflected in the MS Access program.
Do you know which account is being used?
Better to control it at the source 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 7, 2011 at 2:42 pm
I am using windows authentication via an AD group.
I need them to be able to read from 4 views, and read write to 2 tables.
Here is the kicker - I also have the pass through query that imports data into one of the tables that my view is based on. This is the only write I want to allow - I don't want people to be able to just link to this table in access and willy-nilly be able to modify the table.
If I only allow read access to the table and execute on the SP that imports the data into this table, will this work?
Thanks,
sb
October 7, 2011 at 9:19 pm
stephenmbell (10/7/2011)
I am using windows authentication via an AD group.I need them to be able to read from 4 views, and read write to 2 tables.
Here is the kicker - I also have the pass through query that imports data into one of the tables that my view is based on. This is the only write I want to allow - I don't want people to be able to just link to this table in access and willy-nilly be able to modify the table.
If I only allow read access to the table and execute on the SP that imports the data into this table, will this work?
Thanks,
sb
That sounds like the best solution. I wouldn't grant direct access to any tables. Everything would be through views and stored procedures.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply