SQL Server accommodates two types of roles in its security system: database roles and application roles. Application roles are used to setup security at application level rather than at 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.