Hopefully some information will help clear up the confusion.
First, we must realize there more than one security mechanism at play here. At most, there can be three;
- Windows Authentication
- RDBMS Security Model
- Access Security
There are several ways to implement security for specific needs, and that can involve one, two or all mechanisms.
The thing is that each mechanism, at least out of the box, is ignorant of other mechanism, and will not in general interact with other mechanisms. It only cares that its own set of credentials is satisfied, regardless if the other mechanism' sets of credential were. That is a important point here.
Here's a common implementation: Some may not even bother with Access security at all, deigning to put the Access file in a folder managed by Windows permissions so workers who do not need to use that database can't open it, and workers who need it can use it. This implementation is sufficient for where we trust the workers to be honest with their data processing and there is no need for giving different permission upon same table to different groups of worker.
This should hint that the more complex interaction we have with a database, so will be the security.
Going to SQL Server; it's special because it does integrate Windows Authentication with its own security mechanism, so for that context, 1 and 2 in the list can be combined. But merely logging in Windows does not give you the golden ticket to the tables in SQL Server. You still have to connect to the source, and it *will* check your credentials at time of connecting.
In case of Access connecting to RDBMS back-end, Access can allow us to save the password in the connection string. This can be a good or bad thing, depending on our specific security needs. If we never ever want a dialog from the RDMBS to pop up asking user for the password, saving password is certainly one way. This still applies even if we use Windows authentication. In such cases, the password is stored as part of Connection string, which is stored in Connect Property of any TableDef or in Connect column of hidden system object MSysObject.
In cases where we do not want the password for connecting to RDBMS to be saved, we can do so in two ways (or combine both for redundancy):
- Writing the DSN, omitting the password and asking the user to fill it in
- Implement your own login windows and pass it to the connection string
In SQL Server's case of using Windows authentication, you don't actually need to log in but this should still prevent the information about Windows login being stored inside Access.
Where does Access security comes in the picture? Nothing. It's not even relevant because at this point, we're still talking about logging to the RDBMS and accessing the data and thus still in context of RDBMS security mechanism.
Does it mean that there's no point of using Access's security? Hardly. ULS still can be usable as a permissions manager to specific Access object; you could deny all permissions to the tables itself and give users permission to the forms only thus ensuring that they don't edit the table directly. This is something that Windows and RDBMS security cannot stop because once you've already logged in and connected, you are now past their gate. To put this bluntly- they don't know and don't care about the manner of how data is modified as long their *own* credential is satisfied. Thus, Access (and as well as any front-end clients) must manage their own object and ensure that users only go through the proper channels without getting cute and using shift bypass or linking to the table or whatever they come up with circumventing the security.
But that's not the only approach we can take, as this require us implement all (or at least just RDMBS's and Access's) security mechanism and can be labor intensive for little gain (and if company can't trust their employees, then they probably have bigger problems). One lazy implementation could be to require a timestamp and userstamp for any and all data modification, allowing us to stay within RDBMS's security mechanism, and if one user goes rogue, it's on the trail and we can rollback the changes and fire the bastard.
I hope this help illustrate few possible scenarios, and how each security mechanism can be used to provide control over the access, and understanding how they interact (or don't).
