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


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
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.

SUMMARY

This article explains the capabilities, the limitations, and the workarounds for using Microsoft SQL Server Application roles in a Microsoft Access project (ADP).

MORE INFORMATION

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

Access 2002 or Access 2000 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 or the following Microsoft MSDN Web site:

Microsoft SQL Server 2000 Security

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. On the Help menu, point to Sample Databases, and then click 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.
  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 quit the Visual Basic Environment.
  6. Create a copy of the Customers table, and then save it as tNewTable.
    1. In the Database window, right-click the Customers table, and then click Save As on the shortcut menu.
    2. In the Save As dialog box, type tNewTable in the Save Table 'Customers' To box, and then click OK to finish the new table.
  7. In the Database window, click Forms under Objects, click New, and then click OK to open a new form in Design view.
  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 Form view.
  12. Click the command button to run the underlying code. Note 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 NorthwindCS sample 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.
  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 Form view.
  7. Click the command button to run the underlying code. Note that you receive a message box that indicates success.
  8. In the Database window, click Tables under Objects, and then open the tNewTable table.
  9. Modify a record and try to save the changes.
Note 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.

REFERENCES

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:

318816 ACC2000: How to Use Application Roles with Access Projects and SQL Server 2000 Desktop Engine (MSDE 2000)

For more information about GRANT, see the SQL Server Books Online. The SQL Server Books Online is available at the following Microsoft Web site:

http://www.microsoft.com/sql/techinfo/productdoc/2000/default.asp

Modification Type:MinorLast Reviewed:4/19/2005
Keywords:kbProgramming kbADP KbVBA kbhowto KB308312
©2004 Microsoft Corporation. All rights reserved.