0

So I get how to connect Access to SQL Server, and I understand that the tables have to be linked, but my problem is security. If I link the tables from SQL Server into Access, then anyone who comes into the Access forms to enter data can ultimately get at the tables via the link can't they? I know that newer Access versions have no user security, but is there any way to restrict what data they see in some of the tables, or do I need to do that on the back end in SQL Server Express?

1

The easiest way to do that is on the back end. Define a user that has only the access you want and then update the ODBC DSN that Access uses to that user account.

  • I think the OP's point is that no matter what account they set up for Access, the user could get the username/password and connect to SQL Server directly, bypassing the forms. – Aaron Bertrand Mar 27 '15 at 17:09
  • Okay thanks, I will do it on the back end in SQL Server. As a related issue, do people generally create a "dummy" table in access to link to? I want all the tables in SQL Server, so how do I determine which one to pull out and put in Access so I can create the link? – TBrown33 Mar 27 '15 at 17:09
  • I'm not sure if I understand the question. You only have access to the tables defined in the database/catalog defined in the DSN. If you want access to every table on the server (assuming you have multiple databases) I don't know of another way besides defining a DSN for each database. – Queue Mann Mar 27 '15 at 17:15
  • I just meant that I need something in Access to link to right? So I need to have a table back in Access that links to all the "actual" tables in SQL Server (if I'm understanding this correctly). If that is the case, it seems odd to leave 1 table behind in Access as a "link" while all the rest are put in SQL Server – TBrown33 Mar 27 '15 at 17:18
1

You just need to make sure that the credentials used to set up the linked tables only have the bare minimum permissions they need to do the things the forms do.

You will always have to deal with the fact that if you give someone the keys to the door, they will be able to get into your house even when you are not home.

So you need to make sure that if your forms implement any kind of constraints, business logic, etc. that this is repeated in the database, so that it will still be enforced even if users bypass the forms and interact with the tables directly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.