Results 1 to 4 of 4
  1. #1
    edolikian is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    3

    Access 2013 Security on SQL Server connected databases

    Converting to Access 2013 and connect to both SQL Azure and SQL Server databases.

    Understand MDW / workgroup security model abandoned but short of writing a new login script and checking each time an object is accessed, what is the best way to secure access to certain forms/tables/queries using SQL Server security.

    In Access, I have extensibely used User assign to Groups and Access rights assigned to Groups. Thus users have access based on a combination of rights based on their roles.



    All users login to SQL under a single account in my connection string. Any ideas to implement / convert my secured app to Access 2013?

    It seems like this is something many people have to deal with.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You have an mdb front end and you want to create an accdb front end? There are a lot of steps to take that can lock down the Nav Pane, Hide the Ribbon, get rid of the VBA, etc. The problem is that all of this will still leave some vulnerabilities. To address that, you would want to obfuscate your connection strings.

    Work groups may be best handled with different FE files for different groups. I have not tried it, but encryption of the FE file may be a benefit.

  3. #3
    edolikian is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    3
    I actually have a 2003 database that I run using 2003/2007/2010/2013. Being requested to move to Access 2013 or possibly Office 365 and run under most recent version of Access. I just noticed that security is gone and not sure how you even join the workgroup anymore. My thought was to convert to an accdb format.

    Built into my program is to test if certain forms can be opened to determine startup menu and deny access if they try to open a form or query they don't have access to. This is based on their Access login rights. I'm willing to set on SQL side but missing something.

    How do you control rights to Queries/Views in SQL or differentiate between users. I'd still like user to login to Access so that I can mark who is making changes using CurrentUserName etc... LastUpdatedBy etc...

    Do I need to write my own login script?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    As for Using 2013 to use MDB format as a backend, it can be done. Otherwise, 2013 does not like MDB. It is advisable to migrate to an accdb format as long as everyone is using 2007 and newer. I am not familiar with the nuances of 365. I suspect there is not a difference with 365 (not sure), so long you access the accdb from a PC/Desktop. I do not believe 2010 supports User Level Security (or whatever it was called).

    If you are developing a desktop app in accdb format, it should not matter what version you use, 2007, 2010, 2013. The exception is when using Macros and or special features in one version that is not available in another. So you have to choose your architecture approach. If you use macros, you are choosing a tools driven approach. If you use VBA, strictly, you are choosing a maintenance driven architecture. If your users are using different versions of Access, your only choice will be to use a maintenance driven approach. If management is demanding web access via SharePoint and web browsers, that is another story and I am unfamiliar with that story.

    If you want to keep the User from running amuck within your app, you will have to lock it down using various techniques. Some techniques I have listed in previous post.

    If you want to have a User Profile associated with CRUD operations, I use VBA (script) to determine their Windows Login (AD or whatever) and check the User Profile against a table within the DB (tblDept, tblUsers, tblWhatever).

    I think this is the function I use to get user name. There are other approaches that Instantiate Objects to automate AD
    http://word.mvps.org/faqs/macrosvba/GetCurUserName.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-16-2015, 04:25 PM
  2. Replies: 4
    Last Post: 01-30-2014, 12:18 PM
  3. Replies: 2
    Last Post: 08-29-2013, 01:19 PM
  4. Access security ODBC to SQL Server
    By Mark@CHP in forum Security
    Replies: 3
    Last Post: 03-27-2012, 08:31 AM
  5. access on server security
    By bigmac in forum Security
    Replies: 1
    Last Post: 03-11-2012, 06:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums