Security Considerations and Guidance for Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to use Access 2007 to protect the information in your databases. Learn about encrypting files, administering passwords, converting databases to the new ACCDB and ACCDE formats, and using other security options such as SQL Server. (18 printed pages)

Garry Robinson, Office Access MVP and Editor of vb123.com

May 2007

Applies to: Microsoft Office Access 2007

Contents

• Introduction to Security in Access 2007

• Enabling Executable Content in Your Databases

• Making It Simpler

• Navigation Pane Protection Measures

• Protection Using Current Database Options

• Converting Databases to Access 2007 and Removing Jet User-Level Security

• Encryption and Database Passwords

• ACCDE Format: Security for Programmable Objects

• "Industrial Strength" Data Security Using SQL Server

• Conclusion

• Additional Resources

Introduction to Security in Access 2007

Microsoft Office Access 2007 is designed for rapid development and rapid deployment of database solutions. As a result, its interface is accessible to both developers and users. This article explores several ways that you can protect and secure the objects in a database, such as forms, reports, and code. It also examines ways to protect and shield the users of databases so that they interact with the information in the way that the developer intended. The material discussed in this article applies primarily to Office Access 2007 databases, but owners of Access 2000 and Access 2003 databases can also benefit because the Access 2007 user interface can be used with these earlier databases.

Before reviewing the protection options that are available in Office Access 2007, this article examines how you can easily manage the macro protection measures that are applied to the 2007 Office system products and to Access databases, in particular.

Enabling Executable Content in Your Databases

When it comes to macro security settings, Access 2007 database files are designed to behave just like the rest of the 2007 Office system products. That is, if you open a database in Access 2007, you should see the Message Bar, as shown in Figure 1.

Figure 1. The Message Bar in Access 2007

If you trust a database, you can enable the executable content in the database to run. If you do not trust a database, you can still open the database, view the information in it, and run certain Access macro commands. This protection exists to stop code from using Access to perform actions that are harmful to the user's data or computer environment.

According to the Access online Help, executable content is defined as "VBA (Visual Basic for Applications) code and any references in the VBA project, action queries (queries that add, delete, or change data), queries (DDL) that create and alter tables, some Access macro commands, SQL pass-through queries, ActiveX controls, and some unsafe expressions that are stored in object events."

Now you can try an example to see how the macro security settings behave.

1. Open any database (preferably one that has some VBA code in it).

   The Message Bar appears under the Office Fluent Ribbon and displays the message "Certain content in this database has been disabled." At this point, all the executable content is turned off.

2. Click Options on the Message bar.

   You will see a dialog box that has the Disable content check box selected.

3. Select the Enable this content check box, and then click OK.

   The result of these steps is that all the executable content in your database now functions as it was designed by the developer.

Making It Simpler

You might take one look at the explanation in the previous section and understandably say, "I do not want to do that every time I open a database." Microsoft does not want you to do it either. They want to avoid problems when you open Office documents that might contain dangerous content. To make your life easier, the first thing you should do is put your database in a trusted folder or subfolder. The common interface for managing trusted folders for all of Microsoft Office is called the Trust Center.

Trust Center

To help you manage how executable content behaves on your computer, Office Access 2007 and the other Office applications share a dialog box called the Trust Center.

To open the Trust Center, click the Microsoft Office Button in the upper-left corner of the Access window, and then click Access Options. Click Trust Center in the left pane, and then select Trust Center Settings. At first, you are probably only interested in making your database work the way you expect, so select Trusted Locations in the left pane, and you will see the dialog box shown in Figure 2.

Figure 2. Trusted Locations

Trusted Locations

In Trusted Locations, you can define a folder in which you allow all databases to run with no warning messages or executable content turned off. In the Trusted Locations section, follow these steps:

1. Click Add new location.

2. Enter the location (directory).

3. If you need to, select the Subfolders of this location are also trusted check box.

4. If you have to set up a trusted location on your network, select the Allow Trusted Locations on my network check box.

   Note
   Generally, the more specific you can be with network locations, the better the security. Also, if you choose a mapped network drive (for example, a P: drive), it translates to the equivalent UNC path.

5. Enter a description, and then click OK.

All you have to do now is move your database to that location, and all the executable content in that database will work as designed.

Testing for Disabled Content

As you have probably guessed, you cannot guarantee that your users will trust your database. Therefore, you should consider whether it is appropriate to provide a more basic interface for disabled databases through a subset of Access macros. You should also consider testing for disabled content because it will make your database appear more professional.

Try the following simple macro command to see whether your database is trusted (shown in Figure 3).

To determine whether a database is trusted

1. Edit or create an Access macro called AutoExec.

   This macro, if it exists, always runs first when Access opens.

2. Make sure that the Condition column is visible.

3. In the first line of the Condition column, type Not [currentProject].[istrusted]

4. Under Action, select Msgbox, and then type Click on the Options button in the Message Bar above in the Arguments column shown in Figure 3.

5. In a line directly below, type … in the Condition column (to repeat the previous condition).

6. Type Stop Macro in the Action column (or other valid non-executable content).

   From that line in the AutoExec macro onward, all the other macro commands in AutoExec work only if the user has enabled executable content.

Figure 3. Testing that the user has trusted the project in the AutoExec macro

Running an Untrusted Database by Design

In Office Access 2007, you can open databases and view data without having to decide whether you should enable the executable content. You might want to use this approach in your own Access databases. To help you understand the enthusiasm for Access macros, see the following quote from the Access team about the templates in Office Access 2007: "The Access team has built over 25 out-of-the-box applications that use only Access macros. By not using code, we can ensure that these applications are fully functional without signing and without being trusted. They can be mailed around and will work great. In order to make that work, we’ve extended the macro language in some key areas and rethought about the way we build applications."

Even the most die-hard Access VBA developers understand that many Access databases are built without executable content. They appreciate that there is a role for these simpler applications in the overall scheme of things. Even applications that use VBA might still want to use macros for some functionality, such as creating simple navigation that works when the application is running in disabled mode.

Embedded Access Macros

In Office Access 2007, you can now embed macros in form events like VBA instead of saving them in the Macro collection as separate entities. This makes them more portable because you can copy and paste a control with an embedded macro, and the macro remains with the control. In many cases, an embedded macro for opening a report is sufficient instead of a short sequence of VBA for the same task. You can see many samples of these embedded macros in the Featured Online Database Templates in the Getting Started with Access pane that appears if you open Access 2007 without selecting a database. Because most Access macros are not executable content, they are an important tool when you have to make your databases work in all circumstances.

The next section discusses one of the traditional methods of protecting your database objects.

Navigation Pane Protection Measures

One of the simplest protection mechanisms in Access 2007 is to hide important objects. To do this, in the Navigation Pane, right-click an object and select Table Properties or View Properties (for all other objects). Now select the Hidden property, and the object disappears from the Navigation Pane.

To view all the hidden objects, right-click the Navigation Pane (but do not right-click an object), select Navigation Options, and then select the Show Hidden Objects check box. The hidden objects appear but they are partially transparent. Casual and intermediate users generally will not stumble on this, so your objects are protected.

Using Hidden Properties to Provide a Simple User Interface

One very neat solution that helps with navigation is to hide tables and other objects that you do not want users to directly open. Then you can present the database by using the Tables and Related Views group in the Navigation Pane. This solution makes for a much less cluttered interface, as shown in Figure 4.

Figure 4. Navigation Pane before and after hiding objects

Removing Object Design View in Navigation Shortcuts

The Navigation Pane in Office Access 2007 enables you to protect the design of individual objects by using shortcuts. To take advantage of this feature, you can collect a list of shortcuts to objects in the database in a Custom Group. If you then right-click the Custom Group of shortcuts and select View Properties, you can select Hide Design UI (shown in Figure 5). After that, anyone who uses the shortcut cannot open the object in Design View from the Navigation Pane or from any other part of the user interface. To implement this, you must also make that Custom Group the default navigation group for your database users.

Figure 5. Hiding Design View options for an object

Subtle Protection

One of my approaches with hidden objects is to use them sparingly. If more advanced and inquisitive users open a database, and very few items are visible in the Navigation Pane, they could be suspicious and go hunting for the hidden objects. If only a few objects are hidden, users will probably be unaware of the missing objects.

Hiding Object Types in the Navigation Pane

In Office Access 2007, the Navigation Pane contains some extra features (compared to the Database window used in Access 2003) to display and organize objects in the database. One interesting way to protect the objects in the database is to hide complete groups of objects, such as all the tables, as shown in Figure 6. To try this, follow these steps:

To hide object types

1. Click the Microsoft Office Button, and then click Access Options.

2. In the left pane, click Current Database, and then click Navigation Options.

3. Select Object Type, and then clear the check box next to Tables, as shown in Figure 6.

   From now on, if a user selects the Object Type group, tables are not visible.

Figure 6. Hiding tables in the Object Type view

Hiding Navigation Pane Categories

If you want to make it difficult for your users to see certain object views in the Navigation Pane, you can use macro commands to hide Categories and freeze the Navigation Pane layout that you have established. Try the following exercise.

1. Create a test version of your database.

2. Open the AutoExec macro (if you have one), or select the Create tab and then click Macro.

3. In the Action column, type LockNavigationPane. In the Arguments column, for Lock, enter No.

4. On a new line in the macro editor, in the Action column, type SetDisplayedCategories.

5. In the Arguments column, for Show, enter No, and for Category, enter Object Type.

6. On a new line in the macro editor, in the Action column, type SetDisplayedCategories.

7. In the Arguments column, for Show, enter No, and for Category, enter Tables and Related Views.

8. In the Action column, type LockNavigationPane. In the Arguments column, for Lock, enter Yes.

9. Save the macro with the name AutoExec.

   The next time that you open the database, the Object Type group will not be available in the Navigation Pane, and you will be unable to change the Navigation Pane.

Figure 7. AutoExec macro to hide a group in the Navigation Pane

To bypass the hidden Navigation Pane protection that you just set up, you can hold down the SHIFT key as you open the database. Also, if you have trouble editing the AutoExec macro, look for it in the Unassigned Objects group. You must set up and run a second macro with SetDisplayedCategories to Show = Yes if you want to work on the Navigation Pane yourself.

The next section reviews another of the easy protection measures, the Current Database options.

Protection Using Current Database Options

For many years, Access developers could modify the startup options so that users could not directly browse the Database window, use all the powerful menu commands, and press the special keys to access the VBA window and the database container. In Office Access 2007, the Database window has been replaced by the Navigation Pane, and menus have been replaced by the Office Fluent Ribbon. Fortunately, the same useful features of the Startup Options are still with us, but they are now located in Current Database options. This section explains the Current Database options that help protect your database.

Follow these steps to see the Current Database options:

1. Click the Microsoft Office Button, and then select Access Options.

2. In the left pane, click Current Database.

The following sections describe the options that relate to protection.

Display Form

Use the Display Form option (or the AutoExec macro) if you want to display a startup form when your database opens.

Display Navigation Pane

If you clear the Display Navigation Pane check box, the Navigation Pane does not appear when you open the database. If you already use your own navigation system, hiding the Navigation Pane can make the user interface for your database simpler. If you later want to see the Navigation Pane, press F11. If you want to create your own menu navigation system, use the Switchboard Manager on the Database Tools tab.

Allow Full Menus

If you clear the Allow Full Menus check box, the Office Fluent Ribbon is reduced to the Home tab (as shown in Figure 8), and other tabs, such as Create, External Data, and Database Tools, are hidden.

Figure 8. Navigation Pane after clearing Allow Full Menus

Important
When you clear the Allow Full Menus check box, the Access Options are no longer visible from the Microsoft Office Button. To retrieve them, you must hold down the SHIFT key when you open the database so that you can select the Allow Full Menus check box again.

Allow Default Shortcut Menus

If you clear the Allow Default Shortcut Menus check box, the next time that you open the database, Access disables all right-click menus. This option might reduce user productivity, so use it with care. If you want to turn off Design View for reports and forms, you might want to set up shortcuts in a custom group of the Navigation Pane and change the Hide Design UI property that was explained earlier.

Use Access Special Keys

If you clear the Use Access Special Keys check box, your database users cannot use any of the following key sequences:

• F11 to display the Navigation Pane on the left side of the Access window.

• ALT+F11 to open the Visual Basic Editor (VBE).

• CTRL+BREAK to show the current point of execution of your VBA software.

• CTRL+G to view the Immediate window.

Note
This option disables the VBA debugger and related prompts, so make sure that you have added error handling to all your VBA code or you will not receive any error messages.

Enable Design Changes for Tables in Datasheet View

Office Access 2007 lets the user change the design of a table directly from a table opened in datasheet view. Changes that can be made include renaming the column, adding a field, and deleting a field. Generally, you clear this option when you release a database into a live multiuser environment because you do not want users changing the design of a table.

Allow Layout View for this Database

Office Access 2007 has a new way that you can manipulate the design of a form called Layout View. This view enables you to move controls and labels around on the form while it is open in Normal View. Generally, you clear this option when you release a database into a live multiuser environment because you do not want users changing form designs.

The next section reviews the file format differences between Office Access 2007 and earlier versions.

Are you using Access MDB or ACCDB Files?

Before you continue the rest of this article, you must know whether you are running an Access 2000 or Access 2003 database, or an Access 2007 database. The Access 2000 and Access 2003 format databases usually have a file type of MDB, and the Access 2007 databases have a file type of ACCDB.

When Access 2007 opens an MDB or related file, it provides the same functionality as Access 2003 through a collection of Office Fluent Ribbon menus instead of text menus and command bars. The rest of this article focuses on ACCDB format databases. The next section explains how to convert your MDB database to an ACCDB database.

Converting Databases to Access 2007 and Removing Jet User-Level Security

For Access 2007, the Microsoft development team wanted to make Access easier to use. They decided that the internal security system used in Access since the early 1990s made the software unnecessarily complex. Therefore, they deprecated the internal security system in both the ACCDB file format and the new version of the Access Database Engine that accompanies it. This section describes how to convert a database from an MDB file to the ACCDB format and, if necessary, how to set up security in the MDB file so that the workgroup security allows the conversion.

To convert a database from the earlier format to Access 2007 ACCDB format, open the MDB file. Click the Microsoft Office Button, and then select Convert. You can also click Save As, and then select Access 2007 File Format. Generally, this creates the new file, but you might encounter issues that are outlined in the following sections.

MDB Encoding

When you are converting a database, the first thing that you might see is the message "You are trying to convert an encoded database. Decode the database and try again." If you receive this message, follow these steps to create a new decoded database based on the MDB file.

To create a new database based on the MDB file

1. On the Office Fluent Ribbon, click the Database Tools tab.

2. In the Database Tools group, select the Encode/Decode Database option.

   This creates a new database.

3. Close Access.

4. Restart Access and open the new decoded database.

5. Click the Microsoft Office Button, and select the expansion arrow on the Save As button.

6. Click Access 2007 File Format.

Workgroup Security

If you receive the message, "The current user does not have permission to convert or enable this database," read the instructions in the dialog box carefully. You might be using a User account that does not belong to the Admins group, you might not be using any workgroup account, or you might have joined the wrong workgroup file.

To find out which account you are using, on the Office Fluent Ribbon, click the Database Tools tab, and then click Users and Permissions. Next, click User and Group Permissions, and Access tells you which account you are using (see current user). To find out which workgroup you are using, make sure that you have enabled (trusted) the database, open the Immediate window in VBA (using CTRL+G), and type the following.

? SysCmd(acSysCmdGetWorkgroupFile)

Or

? SysCmd(13)

The easiest way to switch workgroup accounts in Office Access 2007 is by using a Windows desktop shortcut that points to the workgroup file of your choosing. If you are using a mixture of both ACCDB and MDB databases, it is probably better that you use Windows shortcuts to open the MDB databases that require a workgroup file.

Note

In Access 2007, if you want to find or permanently change the workgroup file that you are using for your MDB databases, enter the following VBA code in the Immediate window. RunCommand acCmdWorkgroupAdministrator If you want to change workgroups without using the Immediate window, click create a new Access macro, click Show All Actions in the Office Fluent Ribbon, enter RunCommand as the action, and enter WorkgroupAdminstrator as the command. Now click Save so that you can use the macro later.

Workgroup Permissions

Follow these steps to ensure that you have permission to convert the database.

To grant the current user permission

1. Use a Windows shortcut that points to Access 2007 and the workgroup file that secures the database that you are trying to convert.

2. Log on as a workgroup administrator.

   This account must be a member of the Admins group for that workgroup file.

3. Click the Microsoft Office Button, click Save As, and then select Access 2007 File Format.

4. If you do not have all the permissions that are required to convert the database, on the Office Fluent Ribbon, click the Database Tools tab.

5. Select Users and Group Permissions, and then select User and Group Permissions again.

6. Identify the Current User at the bottom of the dialog box.

7. Select the same Current User in the User/Group list above while the User option is selected (shown in Figure 9).

8. Set Administer permission for all the objects types (tables, queries, forms, reports, and macros) in the database for the Current User.

Figure 9. Allocating Administer permission to an object

After you convert the database to Access 2007, quit and restart Access before starting any work on new objects in the database.

Alternative Solution

If you lose the workgroup file, you can try to build a file by creating a blank ACCDB file and importing all the objects from the MDB database into the ACCDB database. Make sure that you view the hidden objects (discussed in the Navigation Pane Protection Measures section) in case some objects have been hidden. Importing at least indicates what is locked in the database by workgroup security.

Unsecured Converted Database

Note that the converted ACCDB database is completely unsecured. Options to protect your database are discussed throughout in this article. Because Access is a popular application, you might find third-party solutions appearing in the marketplace that will provide other ways to protect your database.

After you convert your database to the ACCDB format, a revamped protection method is available to you, which is discussed next.

Encryption and Database Passwords

Since the earliest versions of Access, two of the simplest security measures you could apply to a database were to create a database password and to encode the database. In Access 2007, these two measures have been combined to create a stronger protection measure for ACCDB format files. For the MDB database format, the password and encoding system remains as it was. When you add a database password in Access 2007, Access encrypts the database, with the encryption key being derived from the password. Fortunately, the password is not retained in the file so the file becomes more secure. This style of encryption is common across all 2007 Office system programs.

After Access applies the password encryption, the database file can be opened only after the user enters the password. A benefit of encryption is that users cannot open the database in another program, such as a text editor, and hunt through the file for pockets of valuable information. If you do not encrypt the database, a dedicated person can piece together valuable information from the file.

Making a Backup Copy

Before you encrypt a database, follow these steps to make a backup of the database. Ensure that everyone is logged out of the database.

To make a backup copy of the database

1. Click the Microsoft Office Button.

2. Click Manage Database, and then click Back Up database.

3. Save the backup database into a directory and give it a file name of your choice.

   I recommend saving a backup using the suggested DatabaseName_YYYY-mm-dd convention. For important databases, you might also want to save it to a Windows NTFS-secured directory or on a portable medium such as a CD-ROM.

Opening Databases Exclusively

Before encrypting an ACCDB database, you must open the database exclusively. First, close the database. Then you can open the database exclusively (if no one else is using the database) as follows:

1. Click the Microsoft Office Button, and then click Open.

2. In the Open dialog box, find the file that you want to encrypt.

3. Click the arrow next to the Open button, and then click Open Exclusive, as shown in Figure 10.

Figure 10. Opening a database exclusively

Encrypting Databases and Adding Passwords

When your database is open exclusively, you are ready to encrypt it.

To password encrypt the database

1. Click the Database Tools tab.

2. In the Database Tools group, click Encrypt with Password.

3. In the Set Database Password dialog box, type a password and verify it in the Verify field.

   Note
   The password fields are case sensitive, and there is no way to retrieve this password by using the Access 2007 interface.

Administering Passwords

Applying a password to a database is only effective if you administer password control correctly. To do this, you must educate users to not share their passwords. They also should not write down their database password and stick it on their computer monitor.

If you decide to use a database password and your users change regularly, you should change the password regularly. To help you remember this, include a recurring task in your organizer or diary to remind you to make this change.

Removing Passwords and Encryption

To remove a password, open the database exclusively. In the Database Tools group, click Decrypt Database.

Changing Passwords

To change a password, you must open the database exclusively. In the Database Tools group, first click Decrypt Database before you select Encrypt with a Password.

Passwords and Linked Tables

If you link from one ACCDB database to a table in a password-protected database, you must enter the password before the link will work. If you change a password in another (back-end) database, the linked tables to that database from other databases will fail. In this case, you must delete those linked tables and relink them all again. This is because the Access 2007 Linked Table Manager does not support modified passwords.

You might have noticed that I used the term back-end to describe a database. The next section explains what that means and why it is important.

Splitting Databases and Linked Tables

With Access 2007, you can compile your database to remove source code, and you can secure your data by using Microsoft SQL Server. To implement either of these two significant security approaches, you must understand how to split your database into a back-end, data-only database, and a front-end database that holds your queries, forms, reports, macros, and modules. Doing this enables you to develop your software in a different location from your live database and to install your new front-end solution when your testing is completed.

To try this, follow these steps:

To split a database

1. Back up the database before you start, and consider working on a copy of the database in another folder if you are experimenting.

2. Open the database.

3. Close any open objects in the database so that you are left with the Navigation tab.

4. On the Office Fluent Ribbon, click the Database Tools tab. In the Move Data section, select Access Database.

   This starts the Database Splitter wizard.

5. Read the instructions and click Split Database.

6. Choose an appropriate name that matches the current database and indicates that the database contains only data.

   The wizard indicates this by using a name ending in _BE.ACCDB.

7. Click Split.

If things go smoothly, all the tables will be moved to that back-end database that you created. In place of those tables in the Navigation pane, you will find table icons with an arrow next to them. The database that you started with is now your front-end database.

I recommend that after you split your database, you become familiar with the concept of working with front-end and back-end databases before you implement other protection measures in your database. One feature that you should become familiar with is the Linked Table Manager. You can find it in the Database Tools tab of the Ribbon.

Note
For new developers: the day that you start to understand how to use and develop Access databases by using both live and development folders for front ends and back ends is the day when you move from being just a power user to an Access developer.

ACCDE Format: Security for Programmable Objects

If you want to help secure the forms, reports, and modules in your database, there is no better way to do this than to convert your front-end database to the compiled format, called the ACCDE format. When you compile a database, the readable code from the objects is stripped from the database. Before you can do that, you must do the following:

• Split your database into two databases as described earlier. The first database will hold all the non-data objects (queries, forms, reports, macros, and modules), and the second database will hold all data and data relationships. Make sure that you become familiar with working with split databases in a live situation before you compile a database.

• Have well-established control over who changes objects in the front-end database. If more than one person does this work, you must coordinate how this development occurs.

• Ensure that development occurs on a computer drive that the database users do not have access to.

When you have those things under control, here are the steps to create an ACCDE compiled front end.

To create an ACCDE compiled database

1. Open the development version of the ACCDB front-end database.

2. On the Office Fluent Ribbon, click the Database Tools tab.

3. In the Database Tools group, click Make ACCDE.

4. Enter the name of the file (generally, the same name as the original file with the ACCDE file name extension).

5. Test your ACCDE file.

6. If it works, link the compiled ACCDE file to the live back end.

7. Make sure that your users have all logged out of the live compiled ACCDE front end.

8. Copy the ACCDE database to the target folder and replace the ACCDB front end.

Note
Although the ACCDE format can help protect your intellectual property (forms, reports, modules), it does not provide good security for your tables or queries. For that, you must invest much more time into other solutions, such as Microsoft SQL Server.

"Industrial Strength" Data Security Using SQL Server

Office Access 2007 has evolved into a great tool for creating a modern data management and reporting interface for relational data. Part of the reason for its success is that it is file based and can be easily moved around and modified just like other Office documents. One consequence of this file-based design is that one can easily find and copy the database file, thus making the data security vulnerable. Customizing the database (password, Navigation Pane, and Current Database options) can adequately protect data from accidental misuse, but Access 2007 does not provide high-level security for the data in its databases. Instead, the need for data security would drive users to SQL Server and Microsoft Office SharePoint Server where data security truly works at an industrial level. To help you decide how to secure your data, the following sections provide ideas of what to consider before you embark on an Access-to-SQL Server conversion project. But first, the next section reviews what Windows security can do to protect your database.

Windows Folder Level Security

Before you decide to convert to SQL Server, you should give some thought to storing your shared databases in a folder that is protected by Microsoft Windows security. By using Windows folder security, you can grant permission to the people who use the database as required. I will not go into the details here, but the technique that you have to use with Access databases is to allow users to edit and delete files in a folder. You cannot actually apply the permissions to the database file because of the way that Access manages data locking and compacting. By setting up Windows security, you have a better guarantee of who has rights to use your database because you grant permission as needed. If you do not use folder security, anyone who can access the disk drive that your database is stored on can edit and copy the database. You can learn more about this in the article Restricting Who Can Use Your Database.

When to Use SQL Server

When you first start thinking about how to help secure data, step back and decide which information you must actually secure. For example, if you store important military information or credit card details in your database, you have to store your information in SQL Server from the start. However, if your database evolves in a haphazard way, as many databases do, it might not be as important as you originally thought. Also, your database might not be anything like the original design. Therefore, in most cases it is probably wise to wait until the database design settles down.

At this point, you might decide that the database that you have ended up with warrants only Windows folder-level security or just storage of a couple of critical tables in SQL Server. The reality is that many databases take a while to populate, and many do not turn out the way that they were planned.

Security Benefits of SQL Server

The security benefits that SQL Server provides include the following:

• Your data is no longer stored in a file that is easy to access. When all is said and done, if someone can copy your Access database onto a portable storage device, a determined user can break any protection that you placed on that file. Converting to SQL Server means that your users are unlikely to be granted permission to the underlying files that SQL Server uses to store your data. To ensure this happens, SQL Server is set up so that users do not need any permission to the native files on the server. This means all interaction with the database must occur through SQL Server.

• By default, security is set to the highest possible level. Only the system administrators of the server have access to all databases. For everyone else who needs access to a database, user permissions must be assigned and are not given explicitly. This security is assigned either to Windows User accounts or to special accounts set up specifically within SQL Server.

• Permissions to data can be broken down into rights to Read, Edit, Add, and Delete for individual tables and views (special field and data filters).

• Permissions can be granted to groups of users. This means that you can add a person to a group, and that person will be granted permission to the data just like anyone else in the group. Later, that person can be removed from the group just as easily.

Overall, SQL Server manages rights to the data at a level that Access cannot come close to, even with the user-level security that MDB files support.

Are You in the Mood for Change?

Here are some questions that you should ask yourself before committing to SQL Server data storage and security.

• Are you ready for split databases? Before you think about converting to SQL Server, you have to split your database and understand the mechanics of supporting people who are using linked tables. Converting to SQL Server requires that you put all the tables in the SQL Server back-end database, so you must be fully conversant with working in this type of environment.

• Is your target audience already running SQL Server? This may seem like a fairly straightforward question, but if your target audience is not running SQL Server now, it is a fairly good indicator that the project will be more complex. Conversely, if they are running SQL Server, you might find that support for the conversion will generally be much better.

• Are you trained to use SQL Server? If you do not have SQL Server experience, and you have a complex Access database to convert, you have to practice conversions and start using SQL Server long before you make the conversion.

In my view, I have always found it better to be one step ahead of the user community when it comes to upgrades instead of only arguing the case against the upgrade. Sure, it will not be cheap. Sure, it will be difficult. But at some stage, the security of important data will outweigh the costs of doing the conversion. What is important is that you understand the pros and cons for making the transition so that you provide a balanced opinion.

Conclusion

You can take many different approaches to help protect and secure your database. You might have noticed that some techniques in this article simplify the user interface. So I recommend that you continue to appraise your security as your database becomes more important. If the risk warrants it, start implementing the appropriate protection and security mechanisms.

Additional Resources

This section lists several resources that you can use to learn more about the products and technologies mentioned or used in this article.

• Database Evolution: Microsoft Access within an Organization's Database Strategy

  Luke Chung from FMS Inc. writes on the importance of Access databases in deciding which databases should be transformed to enterprise-level software.

• Exploring Microsoft Access Security

  Article by Frank Rice on security in Access 2002.

• vb123.com

  Learn about Garry’s book, Real World Microsoft Access Database Protection and Security published by APress. The material relates to Access 2003, Access 2002, Access 2000, and Access 97.

• MSDN Library

  Search for "Garry Robinson protection" to find other MSDN articles written by Garry about Access security.

• Access 2007 Resource Center

  Find articles, videos, references, code samples, downloads, and blogs for Microsoft Office Access 2007.

About the Author

Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with his latest postings on Access issues or read his popular newsletter, visit his company’s Web site at vb123.com. This site features Access resources and software that are used by thousands of readers each month. Garry was nominated for the Microsoft Office Access MVP program in 2006 and 2007.

This article was produced in partnership with A23 Consulting.