locked
Setting User Level Security for an Upsized Access DB RRS feed

  • Question

  • Hello,

    I recently upsized an MS Access 2007 DB to SQL Server 2008 R2 with the intent of being able to control user authentication/access privileges to the database tables. After spending days trying to split the Access DB to a FrontEnd/BackEnd setup and configure some sort of a permissions scheme, it appears that what I'm trying to accomplish just isn't possible in Access '07. With that in mind I did some reading on the subject and it appears that SQL is supposed to allow control of what users can see/modify which is great, but unfortunately, I'm not quite sure where to start with this. I've tried doing some general web searches for instructions on what needs to be setup to accommodate this goal on the SQL database that was created but to no avail.

    Essentially, I'm just looking for some information on how I can control table permissions so users can connect to the database and enter data to tables via forms but restrict their access from just opening up a table and making changes directly and prevent them from viewing table data. Any information that could be provided to this end would be greatly appreciated as I inherited this project from the original designer and this is rather uncharted territory for me.

    Tuesday, February 15, 2011 2:41 PM

Answers

  • [quote]how I can control table permissions so users can connect to the database and enter data to tables via forms but restrict their access from just opening up a table and making changes directly and prevent them from viewing table data.[/quote]

    Are you still using Access as a FE?

    If so, you can prevent the users from accessing the navigation pane and the tables by hiding them (look at 'Access Options' in A2007).  Also check out this link from Dev Ashish about manipulating the Access Window.  This way the users can only use the UI you designed and manage the data through the forms.


     

     


    - Doug
    • Marked as answer by Gabelh81 Tuesday, February 15, 2011 6:27 PM
    Tuesday, February 15, 2011 6:03 PM

All replies

  • [quote]how I can control table permissions so users can connect to the database and enter data to tables via forms but restrict their access from just opening up a table and making changes directly and prevent them from viewing table data.[/quote]

    Are you still using Access as a FE?

    If so, you can prevent the users from accessing the navigation pane and the tables by hiding them (look at 'Access Options' in A2007).  Also check out this link from Dev Ashish about manipulating the Access Window.  This way the users can only use the UI you designed and manage the data through the forms.


     

     


    - Doug
    • Marked as answer by Gabelh81 Tuesday, February 15, 2011 6:27 PM
    Tuesday, February 15, 2011 6:03 PM
  • Doug,

    To answer your question, yes, I am using Access as the FE. I perused the link you provided as well but I'm not sure I'm comfortable with the error handling setup when hiding the screen. I just don't know enough about this to competently deal with any issues that can arise without having that screen to see what's going on and navigate in case of trouble. That being said, I was able to figure out the other part of my post which is how to effectively assign permissions through SQL Server. After some experimentation with that, I was able to configure it in such a way that users get an error if they try to access anything for which they do not have explicit permissions. The database creator had used interface manipulation in the past but unfortunately as long as there's a "More Commands" tab anyone with a little ingenuity (and disregard for the rules) can get around that from what I've seen.

    I appreciate your response and for providing the additional info from Dev Ashish. Unfortunately, I'm too much of a database neophyte to attempt something that can complicate troubleshooting so the permissions assignment through SQL is probably my best bet at this time.

    Regards,

    Gabe

    Tuesday, February 15, 2011 6:27 PM
  • Hi Gabe,

    Usually there is more that one way to skin a cat* :-)

    Glad you got it all sorted out.

    Just as an informative note:  What we do here, is set user permissions on the SQL side (Read Only, Read/Write, Admin), and set the more refined security on the FE (including what forms a user can access, and what reports they can run).  We do maximize the UI and hide the Access shell.  Users can not get to the tables directly, only via the UI.

    I wish you continued success with your project.

     

    *my sincere apologies to cat lovers, I did not make up that saying :-)


    - Doug
    Tuesday, February 15, 2011 6:36 PM