SQL*Magic : Super*SQL
|SQL*MagicSuper*SQLCase StudiesResourcesTechnology ChoicesSQL StandardLinks

MS Access User Level Security

Author: Davis Swan

Updated December 15, 2003

Summary: Implementing user level security in MS Access is quite a strange and difficult process. There is a lot of information on this topic available at different web sites, including Microsoft"s, but there did not seem to be a clear and concise summary anywhere. As a result, the following documentation has been provided in an attempt to clarify a few of the issues.

The first, and most important concept to grasp is that user accounts, including passwords, are managed in a system database. However, user privileges for a particular user database are stored and managed in the database itself. Unfortunately, the two processes (managing user accounts and managing privileges) are done through the same user interface in Access, after you have explicitly opened the user database. There is no indication that the system database file is being altered. In fact, you will probably not even be aware of the existence of the system database as you set up the user level security in Access.

Every user on every PC which has MS Access installed on it will have a different copy of the system database. As a result, moving the user database file to another computer, or asking a user on another computer to access the database file from a shared drive will often result in a message such as the following:

You do not have the necessary permissions to use the r:\public\mydatabases\mydatabase.mdb object.

This message will be presented if the particular user really doesnt have permission to open the database, form, or report that they are trying to open. However, in most cases, this message really doesnt have anything to do with the permissions set up in the user database. It actually means that there is an incompatibility between the user accounts in the system database used when the user database was set up and the system database being used by the user trying to open the user database.

In other situations, a database may have been initially set up by the Admin user, but other users with restricted privileges have been added later. A user accessing this database from another computer will probably have full access, including the right to create and delete users, because they will be logging in (by default) as the Admin user. Note that even if the user that created the database had a password on the Admin account, another user on another computer will be able to log into the database directly without supplying a password. This is because they are using a different copy of the system database, which may not have a password set for the Admin user.

The System Database:By default, every user has a system database (often referred to as a work group file) called SYSTEM.MDW stored in a location such as

C:\Program Files\Common Files\System\SYSTEM.MDW

By default, this file will define a single user named Admin and two groups, named Admins and Users. This file will exist for every user on every P.C. When you start up MS Access, the application always tries to log you in as the Admin user first. Only if a password has been set up for the Admin user will you be presented with the Login dialog that requests a user ID and password.

Therefore, unless you have set up a password for the Admin user, every database that you create in MS Access will be owned by the Admin user, and will be completely accessible to anyone on any P.C. (since they will all login to Access as the Admin user by default).

There is NO WAY to change the security on a database once it has been created in the default way. For example, you cannot delete the Admin user from the Admins group, remove privileges from the Admins Group, or remove privileges from the Users group. Setting up new groups or users in an existing database in order to try and implement user level security WILL NOT WORK.

The ONLY way to create an Access database with user level security is to create the database as an Admins user other than Admin and to make sure that the Users group has no privileges. An existing database can be recreated (retaining all its tables, queries, etc.) using the User-Level Security Wizard, but you must first change the information in your system database (work group file).

There is good documentation on how to create a new work group file and set it up correctly on the Microsoft site. In brief, you need to do the following;

  1. Create a new work group file for Access 2000 use the Wrkgadm.exe tool.

Note: Some documentation indicates that you can give your new work group file any name you want to. However, by default, Access will look for the file SYSTEM.MWD in the same directory as your user database file. There are ways around this (by creating a shortcut as described later), but its easiest to just use the default naming convention.

  1. After the new work group file has been created, open up MS Access (with or without opening an actual user database).
  1. Select Tools -> Security -> User and Group Accounts

By default the Admin user is highlighted. Go to the "Change Login Password" tab and add a password to the Admin account. This step is essential in order for Access to prompt you for a User ID and password the next time you use it.

In the User pane choose new and enter a new user name and ID. The actual value for the ID doesnt matter*, but is required if you ever need to recreate the account. You might consider using a standard convention such as the 1st three letters of the users first and last name concatenated together. Alternatively, keep a list of users and Ids.

Immediately add the new user to the Admins group and then remove the "Admin" user from the Admins group.

  1. Exit Access and start it up again, logging in as the new Admins user you just created, not Admin. Note that the new user account will not have a password yet. Go to the Change Login Password tab and add a password to this account.
  1. Now open the database that you want to apply user level security to.

Select "Tools -> Security -> User-Level Security Wizard".

You can accept all the defaults for this wizard except the third screen where you should check off some user security groups that you want to create. You will end up with a secured database. You can then set up new users and groups that have access to particular forms, etc.

There is one last wrinkle with all of this. Other users attempting to open your secured database will be greeted by a pop-up that says the following;

You do not have the necessary permissions to use the r:\public\mydatabases\mydatabase.mdb object.

This is because they are using their own default work group file, not the one that you built for your database. Their work group file will not have the same users set up as the one you created to implement user level security.

You can either have them join the work group by using the Wrkgadm.exe and browsing for your secured work group file, or you can set up a shortcut that explicitly defines the work group file to be used by Access when opening up your particular database.

If you have them join the work group file, then they will ALWAYS be prompted for a User ID and password, even when using MS Access to create a new database for their own use. This can be both confusing and annoying.

If you create a shortcut, the target must include the full path to the Access executable, followed by the wrkgrp switch, the full path to the work group file, and the full path to the database file. An example target is shown below:

J:\Office2000\PFiles\MSOffice\Office\MSACCESS.EXE /wrkgrp "r:\public\mydatabases\system.mdw" "r:\public\mydatabases\mydatabase.mdb"

Note: You will need to specify the System file for any ODBC connections you want to make to a database that has implemented user level security.

For more information, consult the following documents:

The current location for these articles can be found on the Microsoft site.

*Note: anyone that knows exactly how the users are set up in your new work group file, including the exact ID, can duplicate any user in another work group file. Therefore, if you use a standardized approach for creating the ID you are opening up a security hole. For example, suppose you set up the user John Smith with ID johsmi. John Smith then opens up your database and sets a password for his account. He now has secure access to your database. However, if someone knows your convention for setting up user accounts, they can create the user John Smith in another work group file without a password and they will be able to access your database with the same privileges as the real John Smith. The way to avoid any issues in this regard is to use an ID for each user that is impossible to guess.