Hi,
Currently user using connections to both TEST and PROD instances using MS Access, and everything goes well. By using ABC user. Now user having problems with APPS user in TEST . Also, user having the same problem with user XYZ in PROD instance. This user has the ‘SELECT ANY TABLE’ privilege, so it should be able to see the tables, but doesn’t work from MS Access. Please suggest us.
What type is the backend database, and what is the connection type?
If you're able to connect via a SQL connection (I'm assuming ODBC) then you'll be using a predefined set of credentials (i.e. you'll be passing a username and password to Access; probably through the connection string).
Chances are that if your user is unable to get into the database directly, their network (AD?) account is not configured with the same elevated privileges that the ODBC connection has.