Note The information in this topic applies only to a Microsoft Access database (.mdb).
Tip Access 2010 features stronger encryption technology as well as support for third party encryption products. Read an article or try Office 2010!
- Start Microsoft Access.
- Open the database that employs user-level security (user-level security: When using user-level security in an Access database, a database administrator or an object's owner can grant individual users or groups of users specific permissions to tables, queries, forms, reports, and macros.).
- Log on as a workgroup administrator (a member of the Admins group (Admins group: The system administrator's group account, which retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group.)).
- Give the Users group (Users group: The group account that contains all user accounts. Access automatically adds user accounts to the Users group when you create them.) full permissions (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.) on all tables, queries, forms, reports, and macros in the database.
- Open the database.
The workgroup information file (workgroup information file: A file that Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) in use when you log on must contain the user (user account: An account identified by a user name and personal ID (PID) that is created to manage the user's permissions to access database objects in an Access workgroup.) or group accounts (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.) that you want to assign permissions for at this time; however, you can assign permissions (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.) to groups and add users to those groups later.
- On the Tools menu, point to Security, and then click User And Group Permissions.
- On the Permissions tab, click Users or Groups, and then in the User/Group Name box, click the user or group that you want to assign permissions to.
- Click the type of object in the Object Type box, and then click the name of the object to assign permissions for in the Object Name box. Select multiple objects in the Object Name box by dragging through the objects you want to select, or by holding down CTRL and clicking the objects you want.
Note Hidden objects aren't displayed in the Object Name box unless you select Hidden objects on the View tab of the Options dialog box (Tools menu).
- Under Permissions, select the permissions you want to assign, or clear the permissions you want to remove for the group or user, and then click Apply. Repeat steps 4 and 5 to assign or remove permissions for additional objects for the current user or group.
- Repeat steps 3 through 5 for any additional users or groups.
- Some permissions automatically imply the selection of others. For example, the Modify Data permission for a table automatically implies the Read Data and Read Design permissions because you need these to modify the data in a table. Modify Design and Read Data imply Read Design. For macros (macro: An action or set of actions that you can use to automate tasks.), Read Design implies Open/Run.
- When you edit an object and save it, it retains its assigned permissions. However, if an object is saved with a new name, it is now a new object, and so has the default permissions defined for that object type rather than the permissions of the original object.
- Exit and restart Microsoft Access and then log on as Admin.
- Create a new blank database, and leave it open.
- Import all the objects from the original database into the new database.
- Open the database or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
- On the File menu, point to Get External Data, and then click Import.
- In the Files Of Type box, make sure Microsoft Access (*.mdb; *.adp; *.mda; *.mde; *.ade) is selected.
- Click the arrow to the right of the Look In box, select the drive and folder where the Microsoft Access database (.mdb) (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) or Microsoft Access project (.adp) (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) that you want to import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) from is located, and then double-click the database's icon.
- In the Import Objects dialog box, click the tab for the kind of object that you want to import, and then click each object that you want to import, or click Select All to import all objects of the desired type. Repeat this step for each kind of object that you want to import.
To import just the tables' definitions (not the data that they contain), click Options, and then under Import Tables, click Definition Only.
To import select queries (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) as tables (for example, to create a read-only database), click Options, and then under Import Queries, click As Tables. Action queries (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.) import as queries no matter how this option is set.
To include relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.), custom menus and toolbars, or import/export specifications (import/export specification: A specification that stores the information that Access needs to run an import or export operation on a fixed-width or delimited text file.) (Access database only), click Options, and then, under Import, select the items you want included. Microsoft Access won't import a toolbar, menu bar, or shortcut menu if it has the same name as one in the Access file that you're importing to.
- If users will be using the current workgroup information file (workgroup information file: A file that Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) when they open the database, clear the password for Admin to turn off the Logon dialog box for the current workgroup (workgroup: A group of users in a multiuser environment who share data and the same workgroup information file.). This is not necessary if users will be using the default workgroup information file created when they install Microsoft Access.
The new database is now completely unsecured. The workgroup information file that was current when the new database was created in step 6 defines the Admins group for the new database.