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:09:26 -0600

The only thing I can think of is if the recordset is opening
another connection which doesn't have the application role
set. It happens in ADO sometimes where a connection is
created independently of the one that is defined but I can't
remember all the details of when this happens.
You could try monitoring the connections in Profiler and see
if that's happening. It's just odd that you are only seeing
it with one user 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.

Relevant Pages

  • Re: Cannot update a disconnected (and then re-connected) ADO recordset bound to Access 2003 form
    ... opening a new connection object that explicitly has client-side cursors ... adCursorLocation set to adUseClient. ... When updating the disconnected recordset ...
    (microsoft.public.access.formscoding)
  • Re: Persisted Recordsets
    ... Your comment about the CursorLocation property was the key to this. ... application is opened again and no connection can be established with the SQL Server, that it will load the recordsets from disk. ... How can I either store the base table information during the write to disk, or add it to the recordset after it has been read from ... > Connection to adUseClient. ...
    (microsoft.public.data.ado)
  • Help on using Shape
    ... Dim db As Connection ... db.CursorLocation = adUseClient ... Set adoPrimaryRS = New Recordset ...
    (microsoft.public.vb.database.ado)
  • Re: Problem with FIND command and 2000 machine
    ... Set db = New Connection ... db.CursorLocation = adUseClient ... Set adoPrimaryRS = New Recordset ...
    (microsoft.public.vb.general.discussion)
  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)