Does anyone know if there is a way to use SQL Server Application Roles from within Ms Access?
I have an Ms Access application with tables linked to SQL Server. Users of the application can access and manipulate data in the linked tables according to their role in the application. I have also granted users access to the tables on SQL Server, since users authenticate via integrated windows authentication. This however means that the users can also connect to the tables on the server directly, bypassing the protection implemented in the Access application, which is of course not ideal. The Application Roles feature in SQL Server seems to offer a nice solution for this problem, however, there seems to be no direct support for this feature in Ms Access.
An option I was considering is to create a proxy ODBC driver which activates the application role using credentials passed to it in the connection string. However, I have no previuos experience with developing such a solution, so am interested to here what other ideas might be out there!