How To Manage User Permissions In Access Database?

Summary:

In this article, we will discuss how to manage user permissions in Access Database. As this will help you, a lot to specify the level of permission assigned to each individual Access database users.

Rated Excellent on Trustpilot
Free MS Access Database Repair Tool
Repair corrupt MDB and ACCDB database files and recover deleted database tables, queries, indexes and records easily. Try Now!

Download
By clicking the button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

What Permissions You Can Assign To Any Access Database User?

You have the option to manually change object and database permissions for individual users or any group accounts in the workgroup. It is always recommended to allot permission to groups not to any specific users as each user receives the permission assigned to the workgroup.

Permission Types

You can assign each user with 9 types of permission for handling objects or data in the database. Below given table below contains detailed information on 9 user-permission types, applicable to what kind of objects, and what permission it gives to users.

Permission	Allows a user to	Objects involved
Open/Run	Open a database, form, or report. Run a macro.	Databases, forms, reports, and macros.
Open Exclusive	Open a database on a network, while ensuring that others cannot open the database while the first user has it open.	Tables, queries, forms, reports, macros, and modules.
Read Design	View the design of objects. No changes to the design are allowed.	Tables, queries, forms, reports, macros, and modules.
Modify Design	Change the design of objects and delete objects.	Tables, queries, forms, reports, macros, and modules.
Administer	Set database passwords, replicate databases, and change startup properties. Have full access to objects and data, and assign permissions for objects.	Databases, tables, queries, forms, reports, macros, and modules.
Read Data	View data, but not table designs or query designs.	Tables and queries.
Update Data	View and edit data, but not insert or delete data.	Tables and queries.
Insert Data	View and insert data, but not change or delete data.	Tables and queries.
Delete Data	View and delete data, but not change or insert data.	Tables and queries.

Now you must have got enough idea on what type of user permission you should assign for any specific task.

How To Manage User Permission In Access Database?

Follow the below steps to grant or manage user permission in Access database and to its objects.

Choose Tools>Security> User And Group Permissions option, This will open the dialog box of User And Group Permissions.

Switch to the Change Owner tab. Here you can see who is the current owner of the database and all its objects. Go to the Permissions tab and take a look over the name of the database workgroup or users to which permissions are assigned. From the appearing list of user/group names, you have to select the Admin group.

It’s time to Explore detail about the permissions assigned to various group names and object types.
Choose order entry from the list of User/Group Name. Suppose you want to assign permission to this group then follow the following steps.

Now from the appearing drop-down list of object names you have to choose the database. From the Permissions section choose the Open/Run.
Tap to Apply option. After this, all the users who are in the order entry group will get permission to open and run their current database.

It’s time to set permissions for the table objects of the order entry group. Similarly from the drop-down list of an object type, you have to choose table. After that from the appearing list of the object name, select entire table names.
From the Permissions section, select the insert and Update Data option. By default, the option Read Design and Read Data will appear checked to you. You have to uncheck Administer, Modify Design and Delete Data.

Tap the Apply button.
To close the opened dialog box click the ok button.

How To Specify Level Of Access To Each Access Database Users?

Using the user-level security wizard you can specify the level of access to each database user.

To start the wizard, go to the Tools menu, choose the Security option, and then hit the User-Level Security Wizard.
In the opened wizard screen, you are asked whether you want to edit the existing file or want to start with a new security file.

Suppose you want to start with a new one then choose the “Create new workgroup information file” after then hit the Next.

In the next opened screen you have to enter the name and company detail. Well, this step is an optional one. Here you will also see a strange string known as WID. It is a unique identifier that is assigned randomly and it must not be modified.

On the same screen, you are asked whether you need to apply the security setting only the currently editing database wants to apply the default permission to entire databases. After making your choice hit the next.

The next screen defines the scope of your security settings. If you wish, you may exclude particular tables, queries, forms, reports, or macros from the security scheme. We’ll assume you want to secure the entire database, so press the Next button to continue.

In the next opened wizard screen you have to specify the groups for enabling the database.
By selecting each group you can check out what permission assigned to it.
Now you need to assign permission also to the default users group. In this default group, you will get all users of the computer listed here.
In case you are enabling user-level security then most probably you don’t want to allow any rights to the user. So for this, select the “No, the Users group should not have any permissions” After that press the Next button.

In the next step, you have to create a new database user, well you can create as much as a user you want. Just by clicking the “Add new user” option. you need to set some strong and unique passwords for each of your database users.

Don’t create shared accounts. Allowing each database user with an individual account name will increase security and accountability.

After then from the drop-down box, you have to select each user and then assign them with one or more groups. This step will offer each user security permissions which are indirectly inherited from group membership.
At last, you have the option to make a backup of the unencrypted database. This backup helps in easy recovery of your data if you mistakenly forget the password.

It’s a good habit to make a backup, so save it on some removable storage device like a DVD or flash drive. Store this device in some safe location.

After the creation of the backup, delete the unencrypted file from the hard disk for protecting it from prying eyes.

How To Change Already Assigned Default Permissions In MS Access?

You have the option to change the default permission, by making update and allowing new users or group to use database of any specific category.

This will allow other users to view the data that has been returned from the action queries to execute queries. Even if, the permission is not allowed to view table or query in subject.

This option is quiet handy for organization having huge data in their PC.

So any Access database user can use this method for managing and altering default permission set for their resources. All this will ultimately help in easy accessing of the information whenever required.

1: From the tools menu, select the Options tab

2: Tap to the Table/Queries Tab.

3: Hit the Run Permissions which user needs to use.

Precautions to take while assigning default permissions:

If a user selects Owner’s:

Entire users have owner’s query permissions to view or execute query.

Only the query owner is allowed to save changes done in the query.
Only the query owner can easily change the query’s ownership rights.

If a user selects User’s:

Permission given within the user’s classification has the permission to opt it. Users having the Administer permission are allowed to save and make modifications in the query. Such types of users also have authority to change the ownership.

FAQ:

How Do I Restrict Editing In Access?

You can easily prevent users from modifying data by using the control properties of Access form. Here is the complete detail on how to set the control properties so as to prevent the data entry.

Access offers several options to stop users from accidental changing of database forms. The trick is by setting up the control properties named as called Enable and Locked.

Here are the steps that you need to perform:

At first you need to open your Access database form in design.
Make a right click over the Name.
Within the data tab, you have to set the yes option in the Locked property.

Doing this will prevent users from preventing changes in the field. Still users can get into the field and copy that too.

To stop users from editing this field, here are the following steps that you need to perform:

At first you need to open your Access database form in design.
Make a right click over the Customer Name.
Within the data tab, you have to set the No option in the Enabled property.

Even after turning off the Enabled property, you will see that customer name field is still appearing.

To make users work with the form field, you need to set control for both data and its label. So for such situation, follow these steps:

At first you need to open your Access database form in design.
Make a right click over the Customer Name.
Within the data tab, you have to set the No option in the Enabled property. Also set the Locked property to No.

How Many Users Can Use Access Database At The Same Time?

It is a very persistent myth that Access Jet databases only supports 20 users.

But from recent research it is found that around 200 users can simultaneously work on an Access/Jet Database.

If everyone is only using the Access database to view or enter data within the table. In that case huge number of users can easily work in the access database.

But if multiple users are trying to execute massive queries and reports with data updates then performance will get affected.

Go With The Recommended Option:

Unfortunately, due to any Microsoft Access database problem after Window 1o update if your Access database got corrupted. Then for the recovery of lost data from a corrupt Access database, take the help of Access Repair and Recovery solution.

* By clicking the Download button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

This software very efficiently repairs corrupt .accdb and .mdb files. With the help of this, you can restore all your database objects like modules, tables, macros, deleted records, etc.

In fact, the software deliberately works on fixing several Access database errors.

Wrap Up:

In MS Access some permission by default identifies the selection of other objects. Suppose you allow Modify Data Permission in the table, other permissions like read design and read Data will be assigned automatically.

I hope the above detail on how to manage user permissions in Access Database is helpful to you. So try to implement all these things before assigning your Access database to anyone else.

While working with the Access databases you always need to be prepared for incidents like corruption and crashes.

One of the best ways to resolve Access database corruption is to use a powerful tool like Access database repair and recovery.

Still having issues? Fix them with this Access repair tool:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
Click Browse and Search option to locate corrupt Access database.
Click Repair button to repair & preview the database objects.

Pearson Willey

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.