Security strategy: Access client/SQL Server

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
.

Relevant Pages

  • Re: Unable to print to networked printer - get access denied messa
    ... Check the permissions on the server assuming the client has a true RPC ... How is the Standard TCP/IP port configured for the device? ...
    (microsoft.public.windowsxp.print_fax)
  • Re: Office Docs wont Open? and BU Drive not Recognized?
    ... On the server share... ... SHARING tab | Permissions | Share Permissions | Group or User Names ... Client Desktop, which open fine there? ... If I copy the document to the local Client, the document opens ...
    (microsoft.public.windows.server.sbs)
  • Re: Adjusting security setting to run an embedded windows control in IE
    ... the client app need webPermission to connect ... back to the same server and request some data... ... I'm kinda lost in the woods with this permissions... ... >>> The reason it doesn't work in your situation is that when IE creates the>>> AppDomain that it runs your code in, that AppDomain is created based on>> the ...
    (microsoft.public.dotnet.security)
  • Re: Office Docs wont Open? and BU Drive not Recognized?
    ... On the server share... ... SHARING tab | Permissions | Share Permissions | Group or User Names ... Client Desktop, which open fine there? ... If I copy the document to the local Client, the document opens ...
    (microsoft.public.windows.server.sbs)
  • RE: Failed upgrade to SCCM
    ... When you only see 2 actions, that generally means that the client cannot ... I recently started the upgrade our SMS 2003 SP3 Server to SCCM. ... SQL Server security mode and as we require the SQL server to be in SQL & ... however fails at "Install SMS provider components". ...
    (microsoft.public.sms.setup)