Secured DAtabase

[akarra]

Hi,
In my application I have a secured database with exclusive work group created.But as per the modifications we need to make the database access as related to windows single sign on and should remove the exclusive work group and shoud use with system.mdw of windows

how can we accomplish the same. i want to assign roles to the user when create to make sure read-only and admin priviliges are provided appropriately.
thanks very much in advance.

[apr pillai]

What I could gather from your above query is that the Database was secured under a common Workgroup Information File (not under the default System.mdw file). You want to free the database from security settings and bring under the default workgroup information file: System.mdw and make it secure again under this workgroup information file. Am I with you so far?

If you have Administrative privileges for the database now, then it is easy, or atleast you have read permissions to all objects then we can some how manage. First of all let me warn you securing a database under the System.mdw Workgroup information file is not at all advisable. If you need some guidance with Microsoft Access Security then refer the Blog Post: Microsoft Access Security.

If you have atleast read permissions to all objects in the database then do the following, while you are still under the Workgroup Information File you are trying to avoid:

1. Open Microsoft Access.
2. Try creating a new database, you will be asked for your existing UserId and password. You will be the Owner of this database and the owner have full Administrative Privilages (i.e. you can assign permissions yourself or assign Access-Rights to other Users) to the database.
3. Import all objects from the old database into the new database.
4. See that all permissions to all objects are assigned to the Admin Account and to the Users Group Account (by default this will happen but check and confirm).
5. Close the Database.
6. Run the Workgroup Administrator Program C:\Program Files\Microsoft Office\Office\1033\WrkgAdm.exe or Tools-->Security-->Workgroup Administration option in Access2003) and Join the default System.mdw Workgroup Information File.
7. Open your new database (created earlier), you will not be asked for your UserName and password and you will be automatically logged in as Admin user.
8. You must set a password for the Admin user to display the Login control. Select Tools-->Security-->User and Group Accounts-->Change Logon Password and type new password control and verify it in the next control. Leave the old password control blank.
9. You can now create User/Group Accounts as you please in the System.mdw based Workgroup Information file and assign permissions.

NB: Remember, if your database is shared on a network others cannot access the database with the Access privilages you set on your machine. If MS-Office is re-installed on your machine the System.mdw file will be over-written with a new one.

I suggest you better try to understand the basic rules you should know before attempting to implement Microsoft Access Security. Read the following Article and the Comments Section also to take a quick look at the security basics:

Microsoft Access Security