I will be creating an application using MS Access as a client to SQL
Server 2005. Each user will have the client installed on his/her
machine. Some users will be attached to the local network where the
SQL Server resides. Others will access the network via VPN.
I was thinking of using an MDW workgroup file located on a network
server to control access to various user interface elements (i.e.,
forms, reports) using defined groups. In addition, I was planning to
use a single SQL Server login for all clients, just as I've done with
various .NET client apps and web apps.
*Con?: Will this hurt the performance of the application (Access must
now open a workgroup file over a potentially slow WAN connection)?
*Con: The user will have to log in twice -- once to get into the
network and once for the MS Access application.
*Con: I think that permissions on objects are housed in the
application itself (not the workgroup file). Therefore, if we add
permissions for group X to open form A, users would need to get a new
version of the application.
*Pro: A user designated as an "administrator" can easily add new users
and configure group access from the application menu.
As an alternative, I could replicate the functionality of the
workgroup file by creating tables to house lists of users, groups and
objects on the SQL Server. I could then fetch a user's permissions to
various UI elements from SQL Server by passing the user's NT login and
retrieving permissions via stored procedure.
*Pro: Users need log in only once (network).
*Pro?: No MDW file need be accessed over a potentially slow speed
connection.
*Pro: The user would not need to fetch an updated client when
permissions for objects change.
*Con: I would need to create the security structures on SQL Server and
a user interface to manage them
Perhaps there are other alternatives. Can someone give me some
guidance?
Thanks,
Bill E.
Hollywood, FL
