Is there any way to authenticate user in a ms-access database?

Question

I have a MS-access database. But it is on the shared drive. And it is required that only some selected number of people can use it. Is there a way to authenticate the user?

Answer 1

Yes, open the database then run the Tools->Security->User-Level Security Wizard. It will step you through the process of creating a new workgroup file, creating users and groups, and securing the Access objects.

Answer 2

User-level security is not available in Access 2007.

Get started with Access 2007 security offers:

• Trust (enable) the disabled content in a database
• Use a password to encrypt or decrypt a database
• Package, sign, and deploy an Office Access 2007 database

Answer 3

Since Access is file driven, why not create a folder in the shared drive and assign folder permissions to the appropriate people.

Answer 4

If you are on a domain could you not use file level security to prevent users from accessing it?

Answer 5

Two solutions:

1. Place the Access file on a file share with permissions set appropriately. This doesn't work really well if you need to provide read-only access since Access can't write out the temporary .ldb file that it creates when opening an Access file (.mdb).

2. Move the "data" of the Access file to a Sql Server instance... where you can permissions in SQL server to restrict what people can do. We use this method to provide readonly access to the everyone, and then specific people with read-write access. To move the data to SQL you would import it into a new SQL database and then link the tables into the Access file (renaming so that reports/queries/etc continue to work).

Answer 6

Do both. Set access permissions on the directory AND create a new security file for it.

The directory is your front-line security, and limits access on a macro level

The security file can be used to segment access to the various tables, forms, reports, etc. You can even us it give some people read only access and others more full permissions.

Answer 7

I do all of this in VBA. In the switchboard Form_Open sub, Read the user name into a string variable with a windows API, then check to see if the user name in on your list of valid users. If OK, issue a welcome message, if not OK exit Access.

' check user Dim user As String Dim AuthorizedUser As Boolean user = UCase(CurrentUser())

AuthorizedUser = True
Select Case user
    Case "USER_A":
    Case "USER_B":
    Case "USER_C":     
    Case Else: AuthorizedUser = False
End Select

If AuthorizedUser = True Then
   MsgBox "Welcome authorized user " & user
Else
   MsgBox user & "is not Authorized. For access to this database contact User_A"
   DoCmd.Quit
End If