You don't need to use code. You can use two separate mdw files - one for your use when you develop, and one for production use.
Since the production Admins Group isn't the same as the development Admins Group, you can safely add a key person to the Admins Group in the production mdw - they'll be able to add/delete users, but not have 'administer rights' in the mdb.
There is more detail in the security FAQ:
http://support.microsoft.com/?id=207793
Thanks Joan.
Ok, I'm sure screwing something up. For reference, the FAQ
gives these instructions:
33. I want to create a remote site administrator able
to administer the database and add user accounts but
not alter permissions on database objects
You can set this up by using two different workgroup information files: one for development, and one for distribution. You secure your application using the development file, assigning permissions to custom groups (implicit permissions) so that all permissions are inherited through group membership. You use a second workgroup information file to distribute your application. By entering identical group names and PIDs in both workgroup information files, permissions to objects in the database are inherited from the developer workgroup information file for those specific groups. You can then create a site administrator account in the distribution workgroup file and add it to the Admins group. The site administrator account will be able to create users and move them in and out of groups, and will be able to create and delete users and groups, but will not be able to modify permissions to database objects or even to open objects unless the site administrator inherits those permissions
by being a member of other groups. The reason this works is that the remote site administrator is not present in the Admins group of the workgroup file used to secure the database; therefore, this account cannot administer permissions on objects in the database. Unlike the Users group, which is the same across all workgroup files, the Admins group is unique, its SID being encrypted from the strings used to create the workgroup file. The Admins group has Administer permissions that cannot be revoked, but only the Admins group can be used to secure the database. Therefore, the remote site administrator can manipulate user and group information (stored in the workgroup file), but not permissions on database objects (which are stored in the database, which "sees" only the correct Admins group as having the necessary permissions).
This technique works equally well in all versions of Microsoft Access. In Microsoft Access 2.0, work group databases have the extension, MDA; in Microsoft Access 95 and 97, the extension is MDW.
QUESTIONS: How about Access 2002 (this doc. seems to be
written prior to that version)? Does it matter if the
option is set to save in 2000 format?
Here are the steps to follow:
1. Using the Workgroup Administrator, create a workgroup database (DEVELOP.MDA/MDW), which will be the developer workgroup.
QUESTIONS: Do this to an *unsecured* database, or does it
matter? Do this while the database in question is open, or
does it matter?
2. Log on using DEVELOP.MDA/MDW and take all necessary steps to secure your database.
QUESTION: Including running the ULS Wizard, even if this db
has already been secured, sometime in the past?
3. When you create your own custom groups, make sure that you write down the exact names of these groups (case-sensitive) and the Personal ID (PIDs) you use to create them. You will need these strings later.
4. Assign the appropriate permissions to these groups, making sure that you don't grant Administer permissions to any of these groups to any of the objects in your database. Also, make sure that all permissions are removed from the Users group and Admin user.
5. Using the Workgroup Administrator, create another workgroup database (USER.MDA/MDW)- this is the one you will distribute with your application. Make sure you use different strings for the Name, Company Name, and Workgroup ID than the ones you used for DEVELOP.MDA.
QUESTION: Again, do this while the database in question is
open? Or does it matter?
6. In USER.MDA/MDW, create the exact same group names that exist in DEVELOP.MDA/MDW by using the identical case-sensitive names and PIDs you defined in DEVELOP.
7. Create a user account (SiteAdmin) and add it to the Admins group of USER.MDA/MDW. Remove the Admin user from the Admins group. This will make SiteAdmin the administrator for the USER.MDA/MDW workgroup.
QUESTION: "SiteAdmin" is not some sort of a special,
"must-use" name, is it?
8. Put a password on the Admin user, which will force the logon dialog box to appear.
9. Distribute USER.MDA/MDW with your application, not DEVELOP.MDA/MDW. The SiteAdmin account will be able to administer user and group accounts, but will not be able to alter permissions on your database. You should instruct the individual entrusted with the SiteAdmin ID and password not to delete any of the custom groups that you created or your application will "break" and users will not be able to access the objects in the database. If you have to revise permissions, you must log on using the DEVELOP.MDA/MDW as the secured Admins user to make the changes. Just make sure that the users or groups in the User.mda/mdw don't have design permissions on any database object or the database itself.
QUESTION: Is this all compatible with FAQ paragraphs 13 and
14, for the "production" SiteAdmin being able to use the
"Linked Table Manager"?
Appologies for asking so many questions, but like I say, I'm
sure doing something wrong!