Access 97 Upgraded to Access 2007 User level security problem

[Derek Curtis]

I have a customer who was running an application in Access 97 which I then
upgraded to Access 2007 in January. When I upgraded it, I didn't realize
user level security was applied to the database. I guess there have been
problems with some of the functionality of the database for some time, but
they didn't make me aware of it until now. When investigating the problem,
I've found that some of the objects have a different owner and the current
user "admin" has no permissions on those objects. How can I change the owner
of those objects? I tried opening the old access 97 database to see who the
owner of the objects were, but they were all set to admin so I'm not sure
what the problem is.

 

[Chris O''Neill]

What, specifically, is broken? Are you able to change any of the security
settings in the converted database? How about in the old database? In a
database that's been properly secured, it's "correct" that user "Admin" and
the "Users" group would have no permissions to anything. Is there another
user with "administer" permissions (e.g. a SuperUser) and, if so, are you
able to log-on as that user?

Changing the ownership of the objects is fairly easy, but you'll lose ALL of
the security settings and will have to start over. So, it would be better to
fix the problems without mucking with the object ownerships, if that's
possible. But, if things are really a mess, then maybe starting fresh is the
best way to go.

To change the owner of the objects, do this:

1. Create a backup of the old database in case something goes wrong

2. Join the workgroup file (something.mdw) that you want to use for the
database. (Tools --> Security --> Workgroup Administrator). Make sure you
are logged-on as the user that you want to own the database and objects once
you're done.

3. Create a new, blank database.

4. Open the new database and import all of the objects from the old
database into the new database (File --> Get External Data --> Import). Make
sure that the import options include importing all of the data,
relationships, menus and toolbars, etc.

The database and objects within it will now be owned by the user that you
used to log-on to the workgroup file. You'll retain all code and
relationships that were established in the old database. Unfortunately, the
security settings from the old database will be gone (Access considers
imported objects as new objects).

5. Go through the proper steps to re-establish the necessary user level
security settings in the new database

Hope that helps...

Regards, Chris

 

[Derek Curtis]

I am logged into the computer with an administrator account. Most of the
objects in the database have the admin account as the owner, but there is a
small grouping that have another user and the owner and the admin account has
no rights to those objects.

I tried everything you suggested previously; however, when I try to import
the objects, it will not import any of the objects that are have the other
user as the owner. I also tried going back to the old Access 97 database,
yet when I looked at the owner of the objects, every object said it was owned
by the admin user--that's why I don't understand how the objects are suddenly
owned by another user in the 2007 upgrade.

To top it off, Access 2007 doesn't support user level security for accdb
files so there is no mechanism for me to change the owner of the objects in
the accdb files--that is why I am looking for help.

 

[Chris O''Neill]

I'm not familiar with Access '07 at all, so I'm afraid I can't help you there.

In the Access '97 database, you say that all objects are owned by the admin
user? Does the admin user have full permissions to that database? Can you
log-on to that database as the admin user?

It sounds to me like something might have "barfed" during the conversion
process. Asssuming that you have a "goo" backup of the Access '97 database,
I'd maybe start over and try again.

Sorry this may not be all that helpful. Sounds like you've got a mess on
your hands... good luck!

Regards, Chris