MS Access 2000 Network Passwords

[randaj]

Could anyone tell me how to setup passwords for individual databases located on a network? I have several (4) databases on my network and each one needs its own users. I don't want to have to go to each employees desktop and configure there MSDE.

 

[jfischer]

MSDE is a SQL Server product. Microsoft Access is simply a GUI front-end for it, and has nothing to do with MSDE security. Your question appears to concern security for SQL Server.

It sounds like you have several copies of MSDE installed on various workstations on your network. You can administer all of them from a central location. The easiest way to do it is to use the Enterprise Manager (part of the SQL Server Client tools) that comes with a full copy of SQL Server. If you do not have the SQL Server Client Tools, then you'll need to write security scripts and execute them using the OSQL.EXE command line utility that does come with MSDE. The SQL Server Books Online help file that comes with a full copy of SQL Server, or a good book on Transact-SQL, can help you with writing security scripts. The following sample security script is from SQL Server Books Online:

"The following script shows adding a few logins, users, and roles, and granting permissions to the roles."

USE master
GO
sp_grantlogin 'NETDOMAIN\John'
GO
sp_defaultdb 'NETDOMAIN\John', 'courses'
GO
sp_grantlogin 'NETDOMAIN\Sarah'
GO
sp_defaultdb 'NETDOMAIN\Sarah', 'courses'
GO
sp_grantlogin 'NETDOMAIN\Betty'
GO
sp_defaultdb 'NETDOMAIN\Betty', 'courses'
GO
sp_grantlogin 'NETDOMAIN\Ralph'
GO
sp_defaultdb 'NETDOMAIN\Ralph', 'courses'
GO
sp_grantlogin 'NETDOMAIN\Diane'
GO
sp_defaultdb 'NETDOMAIN\Diane', 'courses'
GO
USE courses
GO
sp_grantdbaccess 'NETDOMAIN\John'
GO
sp_grantdbaccess 'NETDOMAIN\Sarah'
GO
sp_grantdbaccess 'NETDOMAIN\Betty'
GO
sp_grantdbaccess 'NETDOMAIN\Ralph'
GO
sp_grantdbaccess 'NETDOMAIN\Diane'
GO
sp_addrole 'Professor'
GO
sp_addrole 'Student'
GO
sp_addrolemember 'Professor', 'NETDOMAIN\John'
GO
sp_addrolemember 'Professor', 'NETDOMAIN\Sarah'
GO
sp_addrolemember 'Professor', 'NETDOMAIN\Diane'
GO
sp_addrolemember 'Student', 'NETDOMAIN\Betty'
GO
sp_addrolemember 'Student', 'NETDOMAIN\Ralph'
GO
sp_addrolemember 'Student', 'NETDOMAIN\Diane'
GO
GRANT SELECT ON StudentGradeView TO Student
GO
GRANT SELECT, UPDATE ON ProfessorGradeView TO Professor
GO

"This script gives the professors John and Sarah permission to update students' grades, while the students Betty and Ralph can only select their grades. Diane has been added to both roles because she is teaching one class while taking another. The view ProfessorGradeView should restrict professors to the rows for students in their classes, while StudentGradeView should restrict students to selecting only their own grades."

 

[randaj]

I am assuming that would work IF I had SQL Server as the backend. We are using Access as the database, small company with limited funds.

 

[lymarist]

You can create a password form and in the event procedure you can use tis command


Option Compare Database
Option Explicit

Private Sub cmdPassword_Click()

Dim m, a, strFormName, str1, str2 As String
Dim Opcion1 As Integer

str1 = "This is a confidential area"
str2 = "Incorrect Password!"
txtPassword = UCase(txtPassword)
a = txtPassword

'Option verify if the password is correct
'1 = correct, 2 = incorrect
Opcion1 = 2

Select Case a
Case "SALES"
m = MsgBox(str1, vbExclamation)
DoCmd.OpenForm "frmMenuSALES"
Opcion1 = 1
Case "CLIENTS"
m = MsgBox(str1, vbExclamation)
DoCmd.OpenForm "frmMenuCLIENTS"
Opcion1 = 1
Case Else
m = MsgBox("Incorrect Password ", vbExclamation)
txtPassword = ""
txtPassword.SetFocus
End Select
If Opcion1 = 1 Then
Me.Visible = False
txtPassword = ""
txtPassword.SetFocus
End If

End Sub

Private Sub Form_Load()
DoCmd.Restore

End Sub

 

[jfischer]

Your initial post mentions that you don't want to go to each employee's desktop and configure MSDE. MSDE is the Microsoft Desktop Engine, a throttled-down version of SQL Server for desktop applications.

Your follow-up post indicates that you are really using a Microsoft Access database, which would be an MDB. If that's the case, there are several types and levels of security that can be set up, depending on how tightly you want to control things. I'd suggest that you go through the Access Database Security FAQ document to determine which level of security is appropriate for your situation.

http://support.microsoft.com/default.aspx?scid=kb;en-us;207793

 

[randaj]

Alright, I read through the Security FAQ, got my Workgroup file setup, now i'm ready to deploy. Do I have to go to each machine and Join them to Workgroup? I placed the MDW and MDB's all on the network.