Results 1 to 4 of 4

Thread: SQL Server permissions related to Access upsizing

  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Question SQL Server permissions related to Access upsizing

    I have an Access 2007 application that is constructed with a front-end and back-end. The front-end has a dynamic menu system where the users login is validated to a permissions table and a switchboard style menu is created based on those permissions, i.e., which forms, reports and update capabilities are available for that user. The system has worked well over time but we now would like to upsize the back-end to SQL Server 2008R2 due to expanded data requirements and additional users.

    I would like to retain the front-end permissions process and connect to SQL Server with a SINGLE connection/login that would allow the app to function as it does now but with the front-end managing permissions not SQL Server. I’m being told that there is not an option in SQL Server to accommodate this approach and that I would need to create SQL Server logins for ALL of the users and set their permissions accordlingly. This would be a major administrative burden to maintain logins in both environments.

    Is this true? If anyone can enlighten me as to options I would appreciate it.

    Thanks,
    Joe

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Then you may have to handle at in app side like create a table with user name and related permissions, check against that table every time user get on app.

  3. #3
    Join Date
    Jan 2015
    Posts
    2

    SQL Server permissions related to Access upsizing

    Quote Originally Posted by rmiao View Post
    Then you may have to handle at in app side like create a table with user name and related permissions, check against that table every time user get on app.
    Thanks for the reply but I am already doing that in the front-end. I'm looking for a way to have a single connection/login to my SQL Server database with full rights but control database functions via MY front-end.

    Joe

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    It's not doable as I know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •