Applicaton role with ADODC

[jobyk]

Hi All

How can I implement SQL Application role with ADODC.

Thanks in Advance
Joby

 

[ClayG]

Frist you grant the application role to users (groups).

The application must execute sp_setapprole with approlename and password to activate the app role. The application has to get the password from somewhere, hardcoded maybe, or ask the user - this is an issue I have never been satisfied with.

 

[gavinhuet]

If memory serves me, the ADODC has a connection string as a property and will use that connection string to connect to the database and then execute the query and make the results available.
This would mean that all queries would have to be 2 part:
first to set the application role;
second to do the actual query.
Obviously this is not such a good idea and rather impractical. Seeing as how you are using ADO you could write a central function, that uses a connection that already has set the application role, and get it to return a recordset, this recordset can then be set to be the ADODC's recordset.
In VB6 it would be something like this:

Set ADODC1.recordset = mysqlfunction(mysql)

I hope this helps.

-- Gavin