How-to #3a
Create a secure Jet (MS Access) database

How to secure an Access database with user-level security

This is explained in the help file for MS Access, but that involves jumping to different sections of the file. This page aims to present this technique in a single document. It assumes you already have a database you want to secure and that you are using Access 97, which must be installed on your machine. This document is probably applicable – possibly with minor variations – to Access 95 and Access 2000 as well, but I haven’t tested it with these versions.

Important – this is a long drawn out technique but it’s very straightforward. Once you have done it once or twice, you start to understand the basics of Access security, and it is worth working through the exercise even if you don’t want to secure a particular database right now. Screenshots are available at important stages where you see the small graphic of a monitor.

1) Create a workgroup file

First you need to create a workgroup file which will contain details of all the users who are allowed to access this database. You (the administrator of this database) will then be able to add or remove users from this workgroup.

Workgroup files are sometimes called ‘system databases’ in Access, and have the extension .MDW (in Access 97). A default file, system.mdw, is created by Access on installation, but you shouldn’t use this file. Create a new file for each workgroup.

To create a new workgroup file:

exit Access
run the Workgroup Administrator, which is the file wrkgadm.exe and which is in your windows system directory (\windows\system on Windows 9x, or winnt\system32 on Windows NT or 2000)
Screenshot
click Create… then follow the instructions on the dialog box
Screenshot
make sure you enter a workgroup ID, and write the name, organisation, and workgroup ID down in case you need it again (you would need this if you ever needed to recreate the workgroup file, without which you might not be able to access your database)
you can save the new workgroup file wherever you like.

2) Join the workgroup

Now run Access. You will automatically be a member of the workgroup you just created, but this has only one member – Admin – and is not secure because it doesn’t require a password to log on. At the moment therefore, you won’t see any difference than before joining a workgroup.

So you need to make Access require a password to log on:

open the database you want to secure
choose Security/User and Group Accounts from the Tools menu. The only user in this workgroup at present is the default Admin user which Access automatically creates for you
on the Users tab of the dialog box, ensure that Admin is highlighted in the Name box
Screenshot
click Change Logon Password, then type a password into the New password and Verify boxes. Don’t type anything in the Old password box (you don’t have an old password yet). Make a note of the password you chose
Screenshot
click OK
exit Access, then run it again to log on as the new Admin account user. Make sure that the Name box reads ‘Admin’ (it may read ‘Administrator’) then enter the password you chose and hit Return
open the database to be secured.

3) Create a new adminstrator account

Now you have to create a new administrator account for yourself. To do this:

open the User and Group Accounts box again (Tools->Security menus)
on the Users tab, click New… then enter your name and a personal identifier – you will need this to log on. (The Access help file tells you about limitations on the size and content of the personal identifier)
Screenshot
click OK

If you look in the User and Group Accounts box, you will see that the new user is only a member of the Users group. You want to be a member of the Admins group, so:

in the Available groups list, select Admins then click the Add >> button; your new account is now an administrator
click OK
exit Access
now run it again, this time entering the name of your new account (not Admin) when you log on. You don’t have to enter a password because you haven’t entered one for the new account yet
so, run Access again and create a password for the new account, just as in step 2

4) Remove the default Admin account from the Admins group

Last step but one. You need to stop the default Admin account being an administrator. Bring up the User and Group Accounts box again. It looks as if you could ought to be able to select the Admin account in the Name list, then click Delete – but if you do this, you will get an unhelpful message saying that the operation could not be completed. This is because the default Admin account cannot be deleted. What you can do is remove it from the Admins group. To do this, select Admin from the Name list, then select Admins in Member of list, then click the << Remove button. Click OK.

5) Secure the database with the User-level Security Wizard

Finally you get to secure the database! Ensure that the database you want to secure is open. Go to Tools->Security, then click User-level Security Wizard (note: this won’t have been installed by a default Access installation. If it isn’t installed, run Access or Office setup again, and ensure that Advanced Wizards are selected in the setup configuration).

The wizard copies the database, encrypts it, and (if you accept the default options) removes all permissions from members of the Users group, if there were any. You need to add these again as the administrator if you want any other users to be able to open and manipulate the database.

And that’s it! Long-winded but not difficult. Of course, only step 5 needs to be repeated for another database if you use the same workgroup. If you want to create a new workgroup, you have to repeat steps 1-4 again first.

To revert to the default Access configuration, where you don’t have to enter a password to log on, exit Access and rerun the Workgroup Administrator and this time click the Join… button. Navigate to the default workgroup file system.mdw, and click OK. You won’t be able to access your secure database again (although you can apparently open it you won’t be able to read or alter any of the contents) until you rejoin the secure workgroup you created in step 1 and log on with a valid user ID and password.

Page last updated: March 14th 2004