I often base my login and access levels (to see forms, tabs on tab controls, and perform certain functions) on the logged in username, which I feel is very secure if you are in any kind of tightly controlled corporate environment where the assumption that network logins are secure is a pretty safe one and where people never share machines.
Grab their username automatically when they open the db, the db always knows who they are and can look up permissions from a table, that way you don't need a username and password. Of course in some environments this wouldn't be as effective and having them type a username and password might make more sense.
One thing I
haven't done perhaps as much as I should have: Looking up all kinds of this type of info when the db first opens, and keeping it open hidden, so that as they work in the db, additional and ongoing dlookups aren't required. As I always say, users generally tolerate better a bit of 'startup' wait, compared to an ongoing slowness, if the tradeoff is to be made--but I am not sure I've always followed my own rule there!
As far as SQL Server, I would always enlist the help of a DBA, unless you can do it yourself, to create groups based on AD membership, to protect server data....perhaps in addition to any security my FE might also be doing. However, if you worry about "well I want user-X to be able to edit data in this SQL table through my app, but NOT any how else"...in that case, the server-based securities don't necessarily solve the problem, and in some cases, it might feel safer to connect to sql server using SA (service account) credentials, on the fly, and don't give anyone access on their own. I've often felt this way because I worry that a user having their own full access, even though we hope they only use my app to mess with data, what's to stop them from opening Excel and creating a connection and doing whatever the heck they want? Not a lot. So sometimes the SA can be a solution.
Recently I've heard that vba project passwords are easy to bypass, which I believe since it comes from experts, but quite a few years ago on UA someone who I thought was an expert told me that they were the "strongest type of encryption" and quite secure, (granted, they were talking about .xls at the time).
I've always felt like the choice between, "use an SA account, which depends on your vba project being secure" vs. "use SQL server security, which means your users could damage things using any ODBC method" is an uncomfortable tradeoff. I think next time I do more Access development I'm going to study up on
@isladogs recommended methods!
atabase Password="MyPassword";
