On your SQL Server–based server, you should:
An attached table must have a unique index to be updatable in Microsoft Access. The Upsizing Wizard can export an existing unique index, but will not create one where none exists. Make sure that tables you want to edit from Microsoft Access are updatable.
You can use the SQL Server Browser to add unique indexes to tables.
The Upsizing Wizard does not export users, groups, or permissions that you have set in your Microsoft Access database. The new SQL Server database and its objects receive a set of default permissions from the SQL Server. Set permissions on the database so that your users are able to access the objects they need.
The default permissions of a new database make it accessible only to system administrators and the database owner.
You can add new users and groups using the SQL Server Security Manager or the system procedures sp_adduser and sp_addgroup. (For more information on adding users and groups, see the SQL Server Security Manager Help file and the documentation of the system procedures sp_adduser and sp_addgroup in the SQL Server Transact-SQL Reference.)
All objects created by the Upsizing Wizard, including tables, triggers, and defaults, are accessible initially only to the database owner and system administrators. This is true whether you upsized to a new or existing database. If you overwrite existing objects, you also overwrite all object permissions.
To grant permissions on tables, use the SQL Object Manager or the GRANT and REVOKE commands. (For more information on setting object permissions, see the section "Managing Object Permissions" in Part 3 of the SQL Object Manager User's Guide, or the GRANT and REVOKE commands in the SQL Server Transact-SQL Reference.)
Although Microsoft Access isn't aware of SQL Server security, it can't violate it. For example, if you're editing a remote table for which you don't have INSERT permission, Microsoft Access lets you type a new record; however, when you try to save it, the server returns an error message, and prevents you from inserting the record.
You can minimize these discrepancies by synchronizing local and remote user permissions and passwords. Then you need log on only once, as Microsoft Access automatically attempts to log in to the server using your local user permission and password and prompts you only if this login fails.
When a remote table is attached in Microsoft Access, the user can save his or her server password locally with the table, and will no longer need to log on when opening the table. This also makes it possible for unauthorized users to gain access to server data. You can prevent this by creating a table named MSysConf.
When a user logs on to a SQL Server database, Microsoft Access looks for the MSysConf table. If it exists, its values control whether users can save their passwords with attached tables, and also control the rate of background population of records. If no MSysConf table exists, default values are used, and users are allowed to store passwords locally.
To prevent users from storing passwords locally
1. Create the MSysConf table.
2. Add a new record.
3. Set the Config column value to 101.
4. Set the value to zero.
These settings disable the "Save login ID and password locally" check box when you attach a server table. To enable the check box, set the value to 1.
Changes made to the MSysConf table will not take effect until the connection is re-established.
The options set in an MSysConf table apply to all Microsoft Access 2.0 applications attached to the table's database.
Protect your work by making your new database recoverable in case it is damaged or lost.
When a database is created on a SQL Server–based server, new records are added to the system tables in the Master database. Dumping the Master database provides you with a backup copy including all the latest changes.
Schedule regular backups of your database so that you can restore your database from this backup copy in the event of a serious problem.
Mirroring a device continuously duplicates the information from one SQL Server device to another. In the event that one device fails, the other contains an up-to-date copy of all transactions.
If you anticipate that many changes will be made to a database between backups and you can't afford to lose those changes, consider device mirroring. Device mirroring is most effective when the devices are located on separate disks, as both devices may be lost if they are on the same disk and the disk fails.