Re: Application Role - aduseclient

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 05/31/02 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Fri, 31 May 2002 15:20:40 -0600

And I don't know if connection/session pooling is
interfering here....but it still seems that other users
would experience some of the same issues. Again, you could
try monitoring the connections though.

-Sue

On 30 May 2002 23:40:53 -0700, veronique.dumon@smals-mvm.be
(veronique dumon) wrote:

>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.