I have  split Access 2007 application that is constructed with a front-end and back-end. Users access the application from local and remote locations.  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. Im 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 or recommendations I would appreciate it.

Thanks,
Joe

Hello,

The way I see it, you should create a SQL Server login inside the SQL Server instance with all the permissions on the upsized database required by the whole application to function properly. You should also manage that permissions table from the database at the SQL Server instance, or you will end having one permissions table per user/computer.

When the user tries to login in your application, you connect to SQL Server and the application database, then query the permissions table, and based on the permissions given to the users, the front-end will continue to provide a dynamic menu.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Thanks Alberto,

I'm not sure what you mean by "SQL Server login inside the SQL Server instance".  I'm not that familiar with SQL Server but are you saying that I should create logins within SQL Server for the 60+ users and grant them complete CRUD rights?

Currently my permissions table is in the back-end Access database so I expect to migrate it to SQL Server.  What you are suggesting is the sort of the approach that I want to use but I was hoping to avoid using the user login to connect to SQL Server.  The problem lies mostly in login management.  Currently it is done by an non-IT Administrative person who manages the Permissions table.  If we have to manage the logins in SQL Server then we would probably have to include a DBA to manage the logins. 

I guess my base question is: If a user opens an instance of the Access frontend using their Windows login is there a way to connect to SQL Server without using their login?  I remember in ancient history (SQL 7.0) where the "SA" was a way to login without providing user details.

Thanks in advance for any additional input.

 Joe

Hello,

Your application should be able to connect with one SQL Server login (other than the sa account) from any client computer, but you should manage the login of users to the application and their permissions based on a table of users and a table of permissions that will reside on the application database on the SQL Server instance.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Sorry for the late response. On vacation without access to internet.

Thanks for the feedback and advice.

Joe