Application Role - aduseclient

From: veronique dumon (veronique.dumon@smals-mvm.be)
Date: 05/31/02 

From: veronique.dumon@smals-mvm.be (veronique dumon)
Date: 30 May 2002 23:40:53 -0700

I have as visual basic application that connects to a SQL-server 7.0
with Windows Authentication. In my database I use an Application Role
to set the permissions on the tables.
In my vb application I use the following code :

Set bestelcon = New Connection
With bestelcon
     .Provider = "SQLOLEDB"
     .ConnectionString = "Data Source=" & server & _
                        ";Initial Catalog=" & db & _
                        ";trusted_connection=Y" & _
                        ";integrated security=SSPI"
     .Open

End With

' user_appl_role and user_appl_pwd are corect
bestelcon.Execute "EXEC sp_setapprole '" & user_appl_role & " ', {
ENCRYPT N '" & user_appl_pwd & "' } ,'ODBC'"

Set db_username = New Recordset
sqlcommand = "select user_name ()"
db_username.CursorType = adOpenKeyset
db_username.LockType = adLockReadOnly
db_username.Open sqlcommand, bestelcon
db_username_role = db_username.Fields.Item(0).value
' the result of these select is the correct name of the application
role

Set kostensoort = New Recordset
sqlcommand = "select * from kostensoort where ks_status = 1 order by
ks_afkorting"
kostensoort.CursorType= adOpenStatic
kostensoort.LockType = adLockReadOnly
kostensoort.CursorLocation = adUseClient
kostensoort.Open sqlcommand, bestelcon

The problem is :
One user has no select permission on the table 'kostensoort' (on
different PC), the others have (on thes PC).
Everyone uses the same application role and the public has no rights
to 'kostensoort'
When the person, who has no select permission in the visual basic
application, runs SQL-QueryAnalyser and first activate the application
role , the select gives results as expected. In QL-QueyAnalyzer,
everything works for this user.
 I think the problem seems to be situated in VB.
Now, I detected that, if I delete the instruction
"kostensoort.CursorLocation = adUseClient"
this user has select permission (and insert/update/delete as set in
the application Role).

The strange is, if I set the following code before the select of
"kostensoort" everything works perfect :
Set db_username = New Recordset
sqlcommand = "sp_helprotect kostensoort, kredietapprole1"
db_username.CursorType = adOpenKeyset
db_username.LockType = adLockReadOnly
db_username.Open sqlcommand, bestelcon
'db_username_role = db_username.Fields.Item(0).value

db_username.MoveFirst
Do Until db_username.EOF
   db_username_role = db_username.Fields.Item(5).value
   Debug.Print db_username_role
   db_username.MoveNext
Loop

Therefor ,what is the problem: the security with the application Role
or the "aduseclient(batch)".
As long as there is no "aduseclient or aduseclientbatch", everything
works perfect for this user.

Why doesn't the "aduseclient" for this person (on different Pc) work
and for the other people it seems not to be a problem ????

PS: With SQLSERVER 2000 this person has the same problem.