Well, there's few options:
1) You can distribute the secured database with .mdw then teach users to click on shortcut which will automatically and silently join them to the .mdw file . This is best way to implement security without affecting any other databases they may have. If they try to open your FE without the .mdw, they'd just be locked out unless they explicitly opened the shortcut.
2) You can just leave the database unsecured, using MSSQL's permissions to deny access. If you don't want to show a form, you can code it so it tries to read the recordset first (which MSSQL will then deny) and pop up an error message. Of course, this does not stop your users from seeing the form in design view or moving around controls. But no matter what, they can't circumvent the MSSQL's security.
3) If you have something that you want to enforce within forms and do not want to have users tamper, while using MSSQL security, the simplest thing to do is just to compile the databae into MDE, which removes all source code and make forms inaccessible to design. Mind, this will not stop users from creating their own tables or queries, IINM. This shouldn't be a concern as long the MSSQL security is properly implemented.
Regarding the horror stories, IMHO, this is because the process is somehow confusing, but if you follow it to the letter, you'll be fine. This is what I usually recommend:
Linky.
Finally, I'm of belief that one should use built-in functionality whenever possible and this is especially true for security. The most ominous thing about security is it's easy to fool oneself into believing it is secured but is actually trivial to break. If it's just a simple UI design to not confuse users, lookup table for usernames is just fine. But to actually secure, it can be circumvented in many different ways.