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