How to use Application roles with Access projects and SQL Server 2000 Desktop Edition

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 role

Microsoft 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:

1. Start Access.
2. Open the Northwind sample Access project.
3. In the Database window, click Modules under Objects, and then click New to open a new module in the Visual Basic Environment.
   
   Note In Access 2007, click Module in the Other group on the Create tab.
4. Type or paste the following code into the new module:

   Public Function AddNewAppRole(RoleName As String, PW As String) As Boolean
   On Error GoTo EH:
   If CurrentProject.IsConnected Then
   Dim sTSQL As String
       'Create the command
   sTSQL = "EXEC sp_addapprole '" & RoleName & "','" & PW & "'"
       'Send the command
   Application.CurrentProject.Connection.Execute sTSQL
   AddNewAppRole = True
   Else
   AddNewAppRole = False
   End If
   Exit Function
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical
   AddNewAppRole = False
   End Function
   

5. Save the module, and then exit the Visual Basic Environment.
6. Create a copy of the Customers table, and then save it as tNewTable. To do this, follow these steps:
   1. In the Database window, right-click the Customers table, and then click Save As on the shortcut menu.
      
      Note In Access 2007, click the Customers table in the navigation pane, click Microsoft Office Button, point to Save As, and then click Save Object As.
   2. In the Save As dialog box, type tNewTable in the Save Table 'Customers' To box, and then click OK.
7. In the Database window, click Forms under Objects, click New, and then click OK to open a new form in Design view.
   
   Note In Access 2007, click Form Design in the Forms group on the Create tab.
8. Add a command button to the new form.
9. Set the OnClick property of the new command button to the following event procedure:

   On Error GoTo EH:
   'Code only works if ADP is connected.
   If CurrentProject.IsConnected Then
       Dim bNewAppRole As Boolean, strTSQL As String
       Dim strRoleName As String, strPW As String
       strRoleName = "AppRoleName"
       strPW = "Password"
       'Call function to create app role.
       bNewAppRole = AddNewAppRole(strRoleName, strPW)
       'Test to see if it failed.
       If bNewAppRole = False Then
           Exit Sub
       End If
       MsgBox "New Application role '" & strRoleName & "' created", vbInformation
       'Create command to grant permissions.
       strTSQL = "Grant Select on tNewTable to " & strRoleName
       'Send the command.
       Application.CurrentProject.Connection.Execute strTSQL
       MsgBox "Select permissions granted on tNewTable for " & strRoleName
   Else
   MsgBox "ADP must be connected to SQL Server"
   End If
   Exit Sub
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical
   

10. Close the Visual Basic Environment to return to the form.
11. Save the form, and then switch the form to the Form view.
12. Click the command button to run the underlying code.
    
    Notice that you receive two message boxes to indicate success. You receive one after the application role is created, and the second one after the new role permissions to tNewTable are granted.

Implementing the Application role

The 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:

1. Used for determining which objects appear in the Database window and for miscellaneous database administrative tasks.
   
   Used for opening tables, views, stored procedures, functions, and the record sources for forms and subreports (but not for the main report itself).
   
   Used for obtaining the record sources for combo boxes, list boxes, and reports.
   
2. Used for opening tables, views, stored procedures, functions, and the record sources for forms and subreports (but not for the main report itself).
   
   Used for obtaining the record sources for combo boxes, list boxes, and reports.
3. Used for obtaining the record sources for combo boxes, list boxes, and reports.

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:

1. In the Database window, click Forms under Objects, click New, and then click OK to open a new form in Design view.
   
   Note In Access 2007, click Form Design in the Forms group on the Create tab.
2. Add a list box to the newly created form, and then set the Name property of the list box to lst_AppRole.
3. Add a command button to the form.
4. Set the OnClick property of the new command button to the following event procedure:

   On Error GoTo EH
       'This avoids a message that no records were returned.
   DoCmd.SetWarnings False
   Dim TSQL
   TSQL = "EXEC sp_setapprole 'AppRoleName', {Encrypt N 'Password'}, 'odbc'"
       'This sets the app role on Connection #2.
   Application.CurrentProject.Connection.Execute TSQL
       'This sets the app role on Connection #3.
   lst_approle.RowSource = TSQL
   lst_approle.Requery
   DoCmd.SetWarnings True
   MsgBox "The application Role is now in effect.", vbInformation
   Exit Sub
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical
   

5. Close the Visual Basic Environment to return to the form.
6. Save the form, and then switch the form to theForm view.
7. Click the command button to run the underlying code.
   
   Notice that you receive a message box that indicates success.
8. In the Database window, click Tables under Objects, and then open the tNewTable table.
   
   Note In Access 2007, double-click tNewTable table in the navigation pane.
9. Modify a record and try to save the changes.

Notice that as you try to commit your changes, you receive an error message about not having enough permissions. This occurs because you gave the new application role Select permission on the tNewTable table, but not
Update permission.

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 considerations

Subforms 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.

VBA security

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.