>>Encountering a bit of a head-scratcher:
>>I've got a VFP application that uses a database in SQL Server. This application resides on a shared fileserver (same server on which SQL Server resides).
>>* User A has no problem
>>* User B gets error indicating problem with opening a table.
>>* Both user A and B are member of Active Directory X
>>* Active Directory group X is registered into SQL Server and assigned db_datareader, db_datawriter and db_owner roles on the database.
>>* The application normally uses a DSn to define connection parameters.
>>* A System DSN has been configured to establish connection to the database, when testing connection it works for both user A and user B
>>
>>As part of the troubleshooting, I've added a bit of code at the program startup that does the following:
>>* Open a connection to SQL Server using SQLSTRINGCONNECT() with connection parameters that are identical to what was configured within the System DSN
>>* After confirming the handle returned by SQLSTRINGCONNECT() is a neither negative nor zero, it issues a SQLEXEC() that queries contents of a table that should be in the database.
>>* modal messagebox is used to display result of the above.
>>* closes the connection.
>>
>>From this troubleshooting code, the behavior indicates that:
>>* When user A is logged on using own workstation, there is no problem.
>>* When user B is logged on using own workstation the connection is succeeding (handle returned is non-zero positive value) but the query fails as if table is not accessible (i.e. error message says table does not exist)
>>* When user A is logged on using user B's workstation there is no problem -- program works fine
>>* When user B is logged on using user A's workstation there is no problem -- program works fine
>>
>>Have tried registering user B into SQL Server and assigned roles of db_datareader, db_datawriter and db_owner for the database -- no change in results.
>>
>>Have restarted the server and workstations in question -- no change in results.
>>
>>Does anybody have any thoughts on what's going on and offer suggestions?
>
>Can you post the query that fails

NOTE: This is a somewhat condensed version of the code in question.

lcConn = "DSN=MyDSN"

hConn = SQLSTRINGCONNECT(m.lcConn)
IF m.hConn <=0 THEN
    * display error
ELSE
    = SQLSETPROP(m.hConn,"ASYNCHRONOUS",.F.)
    = SQLSETPROP(m.hConn,"BATCHMODE",.T.)

    * SYSCONF is a table containing some information for the applicatoin such
    *   as company name, serial number, etc.   Structure is same as would be
    *   with DBF version of the program.  No memo fields.
    nRet = SQLEXEC(m.hConn,"SELECT * FROM dbo.SYSCONF","RV_Sysconf")
    IF nRet <= 0 THEN
        * This is the branch of code that is being executed with some
        *   users.  What is puzzlinb the problem does not occur if the
        *   user logs in on a different workstation.
        WAIT CLEAR
        WAIT WINDOW "Cannot open SYSCONF"
    ELSE
        *
        * some other stuff happens here.
        *
        USE IN (SELECT("RV_SysConf"))
    ENDIF

    = SQLDISCONNECT(m.hConn)
ENDIF

I did at some point try using a DSN-less connection string specifying the "SQL Server" driver and specifying the SQL Server instance. I still got the same puzzling behavior where failure occurs with particular user when logged on using his usual workstation. Problem does not occur if this user logs in using a different workstation.