By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,046 Members | 2,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,046 IT Pros & Developers. It's quick & easy.

Security strategy: Access client/SQL Server

P: n/a
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
Jun 27 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.