Access Security

[Ziggy1]

I have a problem, I was setting up security on my database but now it seems to be affecting my other databases. I was only trying it out on on database to see how it would work, but now every database I open it asks for a password. I can get in just by clicking OK, but I'm confused as to what I did to activate it. Also some databases won't let me modify or open because it says I don't have permission. I did not realize that what I did one databse would affect the others.

I setup myself as a user, and checked off some permissions to see how it would behave.

Does it have something to do with Admin?

Thanks Ziggy

 

[The_Doc_Man]

Microsoft, in their infinite wisdom , has made security an issue for the Workgroup rather than the application. The currently selected workgroup file is defined by your registry, not the .MDB file that you opened. The security (or at least part of it) is recorded in the workgroup file. Certainly, the account information is that way. So when Access runs on some other database, it checks the registry and sees the same .MDW file is still selected. So it applies the same security whether it is appropriate or not.

If you didn't know about the workgroup file then you probably used the default workgroup file. It gets defined when you install Access or the Office suite. This default workgroup file, named SYSTEM.MDW, is PROBABLY located in your windows directory under subdirectory /system32. I've seen it go in a subdirectory of the /ProgramFiles folder, too. Best advice, do a Start >>Find Files or Folders - for file SYSTEM.MDW - and use the one that is in the Winxx/system32 path.

The way to verify that you are connected to this file requires you to run regedit to find all instances of ".MDW" - which is dangerous if you are not good with registries, so DON'T DO IT unless you are very confident. You will find it in the registry at least twice before you get to the entry you want, because the file-classes entries occur before the applications entries. The registry key that tells you which file is selected ends with "SystemDB"

The way around this situation is to do the following steps.

1. Copy the workgroup file to the same directory as your database for which you set up security.

2. Using the Start>>Run option, run wrkgadm

3. Click the "CREATE" option. Let it create a new SYSTEM.MDW in the /system32 subdirectory.

4. Now, again/still using wrkgadm, JOIN that new workgroup.

5. When you want to play with security again, run wrkgadm and JOIN the one you moved to the other directory.

 

[Ziggy1]

Thanks Doc,

This is my work PC so I can't mess around with the registry, would it be safe to assume that if we re-install Access that everything will be back to normal?

Ziggy

 

[Ziggy1]

STILL TRYING TO FIX SECURITY

Ok, I've been trying to avoid fixing this security mess I've created.

I searched for the MDW files and found 3. The 2 system one's like you described, and a 3rd one named "Secured.mdw". It's possible that I created that one unless it is created by access, I can't remember.

I checked another PC in the office and it does not have a 3rd "MDW" file. I deleted the "Secured.mdw". and found that Access would not start without it. So what is causing it to look for the file there? And not in the System.Mdw? I copied the system.mdw to the same folder and renamed it to "Secured.mdw" to replace the one I deleted. This seemed to fix the problem. I no longer get a password prompt and the Admin is the current user.

The problem I have left is that I made some new queries while I was having problems and they show as "Unknown" for Owner and I can't modify them?

The database works on other PC's if I send it there. I restored the Secured.mdw from the recycle bin while I try to figure it out. I think if I recreate the queries with the system copy then everything may be OK?

Thanks for any help or suggestions

Ziggy

 

[Autoeng]

Search for Wrkgadm.exe. Double click on it. Join the system32/system.mdw workgroup then everything should be ok.

Autoeng

 

[Ziggy1]

Thanks Autoeng,

I wasn't sure how to make it point to that file. I think I still have to recreate any objects I created since this screw up, but at least it seems to be working properly other than that.



Ziggy

 

[Autoeng]

You might be able to reset the owner by making a copy of the offending query. Try before scrapping and recreating. And look at this http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=3774&highlight=change+owner

Autoeng

 

[Ziggy1]

Success!!!

Thanks again Autoeng/Doc you saved me alot of agravation.


copying and pasting does not work, the link helped. I imported all the objects showing as "unknown" owner into a new database. Then I deleted the objects in question from the original (copy of) database. Finally I imported the objects from the new database back into the original. Now all the objects show as "Admin" and all work again.

I think I'm going to set up a seperate PC just for testing security next time, Athough I have a better understanding now!




Thanks

Ziggy