Below are instructions I've put together for my coworkers, without the screens shown (stripped them out for this post). Give it a try.
Securing Your Access 2000 FE and BE Databases
Intro
Securing your db (FE and BE) is really quite simple. Access ships with a workgroup
information file (System.mdw). The workgroup file contains your security information. By default, there is one user defined (Admin) and 2 groups (Admins and Users). User Admin is a member of both groups. As a member of the Admins group, user Admin has full rights over all objects in the database.
Goals
To make it simple, all you want the Security Wizard to do is create a new workgroup file, secure all of the objects in your database, and assign a new user to be the owner of the objects. When you're finished securing your database, user Admin should no longer be a member of the Admins group and group Users should have no rights to anything.
After you have secured your FE and BE databases, you will then create one or more groups, assign permissions to the group(s), add users to the workgroup, and assign the users to one or more groups. Note that no permissions are given to the users. Rather, they inherit their permissions from the group(s) to which they belong.
Backup System and FE/BE Databases
To start, find and make a backup copy of the System.mdw file. Also make a backup copy of your FE and BE databases. The reason for this is in case you mess up, you can start over with a clean slate.
Create New User Account (MyNewAdmin )
Open your database and goto Tools | Security | User and Group Accounts. Select the Users tab and select the button New... You want to create a new user who will ultimately be the owner of all of the objects in the database. In this example, I named the new user MyNewAdmin . Assign this new user to the group Admins.
Select the tab "Change Logon Password" and assign a new password to user Admin and type it again to verify it. (Note that at the end of this document, you will remove the password.) Now when you open your database (or any database) Access will prompt your for your name and password. This is because user Admin has been assigned a password.
Exit Access and then reopen your database. When Access prompts you for your name and password, enter the name of the new user (MyNewAdmin ). In this case, we haven't assigned this user a password, yet.
Securing the Front-end Database
Select the Security Wizard via Tools | Security | User-Level Security Wizard..
Check the box entitled Create a new workgroup information file
Press Next
Enter (or browse) the name you want for your new workgroup file (.mdw). I would specify a path to which all users have access (i.e. server). Also, check the box entitled I want to create a shortcut to open my secured database. I usually name my workgroup file: ShareName_Workgroup.mdw
Press Next
DO NOT check the box entitled I want to make this my default workgroup information file. If you do, then your System Registry will be changed to point to this workgroup. Therefore, every database you have created, and will create, will be using this workgroup file rather than System.mdw.
In the next screen, all objects should be checked so that security is assigned to them.
Press Next
On the form that asks you “What groups do you want to include in your workgroup information file”, don't check anything. (We'll worry about permissions later.)
Press Next
Check the box No, the Users group should not have any permissions
Press Next
The next screen asks you to identify the users you want in your workgroup information file. Select <Add New user> (on the left) and enter MyNewAdmin in the box entitled User Name. (If you've assigned a password to this user (we haven't yet), then enter the password.) Now select the button entitled Add this user to the list.
Access displays your NT login name as one of the users. Select your NT login name and select the button labeled Delete user from list.
Press Next
On the next form, check the box Select a user and assign the user to groups. In the Group or user name box, enter MyNewAdmin . And make sure that the group Admins is checked.
Press Next
That's All Folks!
Press Finish.
After Access finishes it creates a report with all of the info needed to recreate the workgroup file. Print it and save it.
Now add a short cut on your desktop and set the properties to this:
"\\Path\MsAccess.exe" "\\path\YourDatabase.mdb" /wrkgrp \\Path\YourWorkgroup.mdwj
Success?
Open your database, via the shortcut, and login as the new user (MyNewAdmin ). Then select Tools | Security | User Groups and Permissions. Then select the tab Change Owner. You should see that the owner of all of the objects in the database is MyNewAdmin . If not, then you did something wrong and you should start over with the original version of the System.mdw and your FE and BE.
To be safe, you could make a copy of your new workgroup file and FE at this point, so if you mess anything up from this point on, you can start back here rather than the beginning.
Securing Your Back-End Database
Close your FE database (do not exit Access because this instance of Access is pointing to your new workgroup file that you just created). Open the backend database via Tools | Security | User-Level Security Wizard.
Check Modify my current workgroup information file
Press Next
On the next form, all database objects should be checked (securing all objects)
Press Next
On the form that says “What groups do you want to include in your workgroup information file”, don't check anything.
Press Next
On the next form, check No, the Users group should not have any permissions
Press Next
On the form that says “What users do you want in your workgroup information file?”, just press Next.
On the next form, check Select a user and assign the user to groups
The Group or user name: should be MyNewAdmin and group Admins should be checked.
Press Next
That's All Folks!
Press Finish
Creating User and Group Accounts
The next thing you want to do is to create one or more groups to which users will be assigned. Goto Tools | Security | User Groups and Accounts. Select the Groups tab and select new. Enter the name of the group.
Now assign permissions for the group (Tools | Security | User and Group Permissions). You will need to assign permissions to objects in both the FE and BE databases.
After you have your permissions assigned to each of your groups, goto Tools | Security | User and Group Accounts and add your users. You don't need to assign any permissions for the users, simply assign the users to the appropriate group. Note that a user can be assigned to more than one group. The user's permissions will be based on the group that has the highest permissions.
At this point you can assign a password to the owner of the database objects (MyNewAdmin).
Launch Microsoft Access (NOT via your new shortcut) and login as user Admin (using the password you gave user Admin earlier). At this point, the workgroup file that is in effect is System.mdw. Goto Tools | Security | User and Group Accounts and select the Change Password tab. Select the button labeled "Clear Password". Now when you open unsecured databases, you will not be prompted to enter your name and password.
