Using Application Roles in SQL Server

SQL Server accommodates two types of roles in its security system: database roles and application roles. Application roles are used to set up security at the application level rather than at the database level. Usually, the application has more rights on the database since actions on the database are controlled. The user only has restricted rights on the database. This prevents users from making unwanted changes to the database using tools such as SQL Server Query Analyzer.

An application switches to an application role after a connection has been established. The way to do this from a DataFlex application is to call the stored procedure sp_SetAppRole. To call a stored procedure, you must use Embedded SQL. The SetAppRole procedure below will switch the connection’s security role to an application role:

//***
//*** Procedure: SetAppRole
//*** Purpose  : Set the application to use the application role
//*** Arguments:
//***   - sAppRole  The application role name
//***   - sPassword The password for the application role
//***   - hFile     The handle of the SQL Server table already open
//***
Procedure SetAppRole String sAppRole String sPassword Handle hFile
    Handle hoSQL
    Handle hdbc
    Handle hstmt

    //*** Create the handle manager, we want to call a stored
    //*** procedure using ESQL
    Get Create U_cSQLHandleManager To hoSQL
    If (hoSQL <> 0) Begin
        //*** Connect
        Get SQLFileConnect Of hoSQL hFile To hdbc
        If (hdbc <> 0) Begin
            Get SQLOpen Of hdbc To hstmt
            If (hstmt <> 0) Begin
                Send SQLSetProcedureName Of hstmt "sp_SetAppRole"
                Send SQLSetArgument Of hstmt 1 sAppRole
                Send SQLSetArgument Of hstmt 2 sPassword
                Send SQLSetArgument Of hstmt 3 "None"
                Send SQLCall Of hstmt
                Send SQLClose Of hstmt
            End
            Else ;
                Error 775 "Unable to open a statement handle."
            Send SQLDisconnect Of hdbc
        End
        Else ;
            Error 776 "Unable to connect."
        Send Destroy Of hoSQL
    End
    Else ;
        Error 777 "Unable to create Embedded SQL handle manager."
End_procedure // SetAppRole

For more information on application roles, see the SQL Server documentation. For more information on Embedded SQL, see Using Embedded SQL.

See Also

• Techniques