Setting up security 2

I don't have a general website, but just to get you round this particular problem, try the following.

Find the system.mdw file on your own c drive. This is your workgroup file, and it contains all the users/passwords/permissions you have set up. Access uses this by default the minute it starts up. This also means that it uses it no matter what database you choose, which is why it's securing all databases. However you can set up an icon for a database so that it uses a different workgroup file for that access session.

1. Copy your own workgroup file (system.mdw) to the shared network drive. Rename it to reflect your application: application.mdw.
2. Then on the icon for the database, insert the following command line:

"C:\PathToAccess\MSAccess.exe" PathToDB\DBName /wrkgrp PathToSharedDrive\application.mdw

(See Access Help on Command Line for more info on this)

3. Check that from another user's PC this kicks off the database, and starts up security.

4. Back on your own PC, start up Access as normal (so that it is picking up your original workgroup file), and clear the Admin's password, so Access will stop asking you to log in when you are using your access.

Bear in mind you will have to control things tightly when making changes to permissions.

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.

SarahG and FancyPrairie, Thank you both. This is a great help. SarahG helped me understand what Access was trying to do and FancyPrairie gave a great step-by-step help sheet. It should be in the Access help topic!! Set something up to point to this thread. It will be a help to others!
Thanks Again.
Bill