SQL -99 error while viewing a listing
This error is sometimes seen while viewing a listing in InterSystems IRIS Business Intelligence:
ERROR #5540: SQLCODE: -99 Message: User <USERNAME> is not privileged for the operation (4)
As the error suggests, this is due to a permission error. To figure out which permissions are missing/needed, we can take a look at the SQL query that is generated. We will use a query from SAMPLES as an example.
SELECT TOP 1000 %ID, DateOfSale, Outlet->City, %EXTERNAL(Channel) AS Channel, Product->Name AS Product, UnitsSold, AmountOfSale AS Revenue, (Discount * 100) || '%' AS Discount, Comment FROM HoleFoods.SalesTransaction source WHERE source.%ID IN (SELECT _DSsourceId FROM HoleFoods_Cube.Listing WHERE _DSqueryKey = 'en1988570802') ORDER BY DateOfSale,%ID
We can see that there are two tables being queried here: HoleFoods.SalesTransaction and HoleFoods_Cube.Listing. HoleFoods.SalesTransaction is labeled as "source" and HoleFoods_Cube.Listing is producing IDs for the WHERE clause. Typically the source table is the main table the cube is based on. The Listing table is generated by IRIS BI. Users need to have SELECT privileges on both of these tables to be able to run a listing.
There are cases where more permissions may be necessary. If a Custom Query is being used, there may be additional tables that need permissions. Other times stored procedures may be used which also may need additional permissions.
The WRC is always available to assist in debugging permissions and any other issues that you may be seeing.