Article ID: 308312 - View products that this article applies to.
This article was previously published under Q308312
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access project (.adp).
For a Microsoft Access 2000 version of this article, see 318816
This article explains the capabilities, the limitations, and the workarounds for using Microsoft SQL Server Application roles in a Microsoft Access project (ADP).
In SQL Server, you can create database roles for easier administration of permissions in a database. Instead of granting individual permissions to each user separately, you can group users with the same permission needs by making them members of the same regular database role, and then assigning permissions to the database role itself. Unless a specific permission is explicitly denied elsewhere, member users will acquire the permissions granted to that database role.
While regular database roles are very useful for situations where you want users to be able to perform their own ad hoc queries or updates to a database, they are not always appropriate. Sometimes, you may want users only to have certain permissions when they use a specific application, and you do not want to be able to view or modify data outside the application.
One method that is often used for working around this is to only give the necessary permissions to one SQL Server user account. The actual users might have permissions to connect to a database but not to view or modify any data. After a user connects to the database using the user's individual account, the ADP could then programmatically reconnect using the credentials of the user account that does have permissions. While this can be effective, it does not allow you to distinguish between users in the database or to determine which user performed a particular action.
Application roles are designed to work around this limitation. Application roles, unlike regular database roles, do not have members themselves. Instead, users log on to a SQL Server and connect to a database using their own credentials. At that point, the security context of an application role can be applied programmatically to an existing connection by using the sp_setapprole stored procedure. In SQL Server, individual users are still differentiated but the permissions that are available within a particular connection are limited to the permissions of the application role. The user's individual permissions, whether lesser or greater, are no longer considered.
Creating an Application roleMicrosoft Access projects do not have any visual design tools for creating SQL Server Security objects such as application roles. Microsoft recommends that you use the client tools that are included with the regular version of SQL Server or Microsoft Office XP Developer for creating the application role and assigning it permissions. However, you can still create the application role and grant it the necessary permissions programmatically by using Transact-SQL (T-SQL) from an ADP. Although a full discussion of SQL Server Security is outside the scope of this article, additional information can be found in SQL Server Books Online The following steps show you how to programmatically create an application role and grant the new role Select permissions on a table:
Implementing the Application roleThe main complication when you are using application roles in Access projects is that Access uses three connections to SQL Server to handle various tasks. Ideally, to apply an application role to the whole project, you would have to execute sp_setapprole in the context of all three connections. The objects handled by each connection are as follows:
Although connections #2 and #3 can be accessed fairly easily, there is no method available for executing the stored procedure in the context of connection #1. Fortunately, this connection is the least important of the three and is easily worked around by constructing your own user interface (for example, a switchboard-type form) for handling database objects instead of relying on the built-in Database window.
The following steps use the Northwind sample Access project to demonstrate how to apply an application role against connections #2 and #3:
By design, Access only shows objects in the Database window for which the user has at least Select or Execute permissions. Access uses connection #1 to determine which objects a user has permissions for. After applying the application role to connections #2 and #3, the Database window still shows the same objects that it did before, even though the user may no longer have permissions to all the objects, or may have permissions to more objects that are not shown. This can result in unexpected behavior when you use the Database window.
For example, when you opened the tNewTable table, it "appears" that the user does have permissions to edit and insert records. The insert new record icon at the bottom of the table is enabled, and the user is able to put a record in edit mode. You do not see any visual clue to indicate otherwise until you try to commit the edit or insert, which results in an error message. Access believes you have permissions when you actually do not.
The most effective workaround is to provide a custom interface for the user and not to rely on the Database window. By using a switchboard-type user interface, you can control exactly which objects the user has access to.
Other limitations and security considerationsSubforms not working
Unlike with other database objects, Access does not always use the same connection to retrieve the data source of a subform. Access frequently (but not always) creates a new connection to SQL Server just to handle the subform recordset, or to retrieve the linking field data that connects the subform to the main form. Because this new connection does not have the application role applied, a permissions error may be generated if you do not have explicit permissions to the database object. Unfortunately, this means that there is no reliable way to use bound subforms when application roles are applied. The only effective workaround is to have completely unbound subforms, with the data manipulation handled programmatically. This is the most serious limitation when using application roles in Access.
Reports not working
When you have an object such as a table or a view name listed as the record source for a report or subreport, Access checks to see whether the object is listed in the Database window before retrieving any data from SQL Server. Because the Database window uses a connection that does not have the application role applied, an error is generated if you do not have explicit permissions to the underlying data source.
To work around this problem, always use Transact-SQL statements as the record source for forms and reports. For example, use "Select * from ViewName" instead of just "ViewName" or "Exec StoredProcedureName" instead of just "StoredProcedureName." This way, Access passes the Transact-SQL statements directly to SQL Server and retrieves the data based on the permissions of the application role.
The Public Database role
An application role acquires the permissions of the Public database role. By default in NorthwindCS, the Public role has full permissions to most objects. Therefore, an application role is generally ineffective. When you created the tNewTable table in the "Creating an Application Role" section, the Public role was not granted permission to the table, and you later saw the effects of the application role security context on that table. However, other tables may not show any difference under the application role because the Public role has permissions to those objects.
Because the password for the Application role is embedded into the application from which it is called, a knowledgeable user would be able to read the application role name and password from the source code, and then use that information to gain access to SQL Server from another application. Therefore, it is a good idea to compile the ADP into an ADE file so that the source code is not viewable. At minimum, enforce a password on the VBA project.
For additional information about a Microsoft Access 2000 version of this article, click the article number below to view the article in the Microsoft Knowledge Base:
318816For more information about GRANT, see the SQL Server Books Online. The SQL Server Books Online is available at the following Microsoft Web site:
(https://support.microsoft.com/kb/318816/EN-US/ )ACC2000: How to Use Application Roles with Access Projects and SQL Server 2000 Desktop Engine (MSDE 2000)
Article ID: 308312 - Last Review: March 29, 2007 - Revision: 6.2