Published on InterSystems Developer Community (https://community.intersystems.com)

Home > WinSQL Lite: Unable to load tables in WinSQL Lite

Question
Steve DiQuattro · Jun 9, 2021

WinSQL Lite: Unable to load tables in WinSQL Lite

I have created a custom role in IRIS for users to provide limited view-only access for querying tables in the HSANALYTICS namespace. WinSQL has been installed on a Windows server (WinSQL Lite version 14.0.244.784) and an ODBC connection entry has been created in WinSQL for the users to log in and run SQL queries. The custom role provides the roles/resources included in the attached file. The WinSQL System DSN entry defined is:

Health Insight UAT HSANALYTICS    64-bit    InterSystems ODBC35

The ODBC35 driver is defined as:

InterSystems ODBC35    2018.01.00.184    CACHEODBC3564.DLL    9/19/2018

When a user with the custom role connects to the database with WinSQL the catalog does not load/display the available database tables. Performing a refresh does not remedy the problem.  But the user can successfully run SQL queries if he/she knows the name of the table(s). However, a user that is assigned the %All role has no such issues...tables will load and display. It seems that it must be a permissions issue but I am not sure what other roles/resources are required for the customer role to allow the tables to be loaded and viewable in WinSQL. I was wondering if anyone else has seen this problem and might have any suggestions.

Thank you,

Steve Diquattro

#ODBC #SQL #InterSystems IRIS
Product version: IRIS 2019.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux for x86-64) 2019.1.1 (Build 612_0_20518U) Fri Dec 18 2020 10:20:04 EST [HealthConnect:1.1.2]

Source URL:https://community.intersystems.com/post/winsql-lite-unable-load-tables-winsql-lite