Call SQL Function via API

[shadowinc]

Background - I created a SQL function with the name schema.function_name, which returns a table, in a notebook, the function works perfectly, however, I want to execute it via API using SQL Endpoint. In API, I got insufficient privileges error, so granted it SELECT access to the function. I am still getting the same error: insufficient privileges.

We checked with our Admins and they couldn't locate the function to check its privileges. With the Describe Function you can't see the location of the function.

Any idea where the functions are stored or how to grant necessary privileges so that it can be used in the API?

Note: We are not using Unity Catalog.

[AbhaySingh]

Do you know if API service principal / user has USAGE on the database itself? This seems like the most likely issue based on information on the question. 

Quick Fix Checklist:

  Run these commands in order (replace api_user with the actual user from step 1):

  -- 1. Find the API user

  SELECT current_user();  -- Run this through your API first!

  -- 2. Grant database usage

  GRANT USAGE ON DATABASE your_schema TO `api_user`;

  -- 3. Grant function select (you already did this)

  GRANT SELECT ON FUNCTION your_schema.function_name TO `api_user`;

  -- 4. Find and grant access to underlying tables

  SHOW CREATE FUNCTION your_schema.function_name;  -- Check what tables it uses

  GRANT SELECT ON TABLE your_schema.table1 TO `api_user`;