Permission Errors

After you’ve set up your security permissions in the database, you’ll want your application to conform to them and to handle any errors that arise when a user doesn’t have permission to do something he or she is trying to do.

There are a couple of ways to reduce the incidence of errors when your application is running. One easy way is to use the sp_helpuser stored procedure to determine the group to which a user belongs at the beginning of the program. Then you can disable menu options that you know he or she may not use. Although this gets around a lot of errors, it also means that you'll have to alter, recompile, and reissue your program if the security settings for that group are revised later on. If you are really set on using the sp_helpuser procedure, you can encapsulate the rules into a Remote Automation server and then call this server from your application. All of these layers, however, do end up slowing down the application.

You can also call the sp_helprotect [sic] stored procedure to determine what permissions a user or group has been assigned for a table, view, or procedure that a user is about to use. But this can be time-consuming; you’ll have to check all of the permissions that have been granted to and revoked from the public group, the user’s group, and finally the individual in question. If you are dealing with column permissions, the results of the sp_helprotect procedure can be quite long.

Even if you try to code to avoid errors, you’ll still get errors cropping up from time to time. It’s vital, therefore, for you to understand a little about the rdoErrors collection and how to find out what’s gone wrong with your SQL statements. A common cry from the Usenet newgroups is, “I’ve got an ‘ODBC Call Failed’ error. What went wrong?” The answer is to look at the other error messages in the rdoErrors collection. The ODBC Call Failed message is just one in a series of messages; you have to read all of them to find out what went wrong.

Public Function ErrorHandler(ByVal lngErrorNum As Long, _
    ByVal strDescription As String, _
    ByVal strProcName As String, _
    ByVal intIcon As Integer, _
    ByVal intButtons As Integer)

    Dim strMessage As String
    Dim errError   As rdoError

    strMessage = "Error " & lngErrorNum & vbCrLf & _
        strDescription & vbCrLf & _
        "Occurred in " & strProcName

    For Each errError In rdoEngine.rdoErrors
        strMessage = strMessage & vbCrLf & vbCrLf & _
            "rdoError " & errError.Number & vbCrLf & _
            errError.Description & vbCrLf & _
            "Source " & errError.Source & vbCrLf & _
            "SQL State " & errError.SQLState & vbCrLf & _
            "SQL RetCode " & errError.SQLRetcode
    Next errError

    rdoErrors.Clear

    ErrorHandler = MsgBox(strMessage, _
        intButtons + intIcon, App.Title & " - Error")
End Function

Here’s a function that you can call from all of your error handlers; it will list all the rdoErrors in complete detail. If you use it to test your application, you’ll find out quickly what’s going wrong, and you’ll be able to code more helpful messages at appropriate points in the application.