I just completed migrating an Access backend to SQL Server 2008. The way that the frontends are set up is as follows. A copy of the frontend sits on a network drive and anyone that has access to that drive can open and modify it. We have multiple frontends for different branches of the company so that certain fields could originally be locked from one group but not from another that has a higher access on a network drive.
Now the question I have is how do I allow different users to modify the same frontend without putting every user in the backend? For the moment the frontends are going to stay the way they are with Windows Authentication to get to the file itself (soon we will be moving to a web based frontend). I just don't want to have to add every user manually into the backend because that seems like it would cause problems. What I want is to just use a generic login for the DSN so that any user that clicks on it will be logged right in and not asked for the password.
If you users belong to a network group that has access to that file location, then you could grant the group access to the SQL Server database.
Otherwise, check into setting up Application Roles in sql server. These work by granting permissions to a specific application rather than a specific user.
If it's not practically useful, then it's practically useless.