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
SQL Server permissions related to Access upsizing
Quote:
Originally Posted by
rmiao
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
