Article ID: 318816 - View products that this article applies to.
This article was previously published under Q318816
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access project (.adp).
For a Microsoft Access 2002 version of this article, see 308312
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.
Although regular database roles are useful for situations where you want users to be able to perform their own queries or updates in a database, they are not always appropriate. Sometimes, you may want users to have only certain permissions when they use a specific application, and you do not want them 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 by using the user's individual account, the ADP could then programmatically reconnect by using the credentials of the user account that does have permissions. Although 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 by 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 roleAccess 2000 has a wizard for creating application roles from the Access user interface. To use this wizard, you must have MSDE 2000 or the SQL Server 7.0 Client Tools installed on the local computer. However, if you do not have MSDE 2000 or the Client Tools installed, you can still create an 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 or in the following article on the Microsoft MSDN Web site:
Microsoft SQL Server 7.0 SecurityThe following steps show you how to create an application role and how to 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 main 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 main connections and any other connections that are used by Access. The objects handled by each main 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. You can easily work around it by constructing your own user interface for handling database objects instead of relying on the built-in Database window. For example, you could construct a switchboard-type form.
The following steps use the NorthwindCS sample project to demonstrate how to apply an application role against connections #2 and #3:
By design, Access shows only 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. It does so, even though the user may no longer have permissions to all the objects. Or, the user 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. This 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, the user may receive a permissions error if the user does 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 you use application roles in Access.
Reports not working
When you have an object such as a table or 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 it retrieves any data from SQL Server. Because the database window uses a connection that does not have the application role applied, the user receives an error if the user does 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." This way, Access passes the T-SQL statements directly to SQL Server and retrieves the data based on the permissions of the application role. However, stored procedures will not work as the record source for reports when you use application roles. To use stored procedures with reports, you must upgrade to Access 2002.
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. Later you 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 in 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.
SQL Server Books Online is available at the following Microsoft Web site: