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 "administra tor" 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
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 "administra tor" 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