CRM Read-only DB user - Stored procedure/functions

Chris Francis

We are in a cloud CRM environment. I'm trying to pulling data to a local location with the read-only DB user. Most of the data I can pull fine. However, I can't pull stuff from various stored procedures (USP_DATALIST_JOURNALENTRIES) or functions (UFN_PLEDGE_GETBALANCE, UFN_PLEDGE_GETPASTDUEAMOUNT, …) with the read only user. Our RWE users have no problem accessing those in our test environments.

Here are the workaround I'm considering, none of which I really like:

• Put the missing info into a datalist so I can do an API call for the data.
  • This will be slow and will make a request per gift
• Turn my full SQL into a datalist so I can pull it all in one API call
  • Fine, but will be significant effort to rework my logic into a stored procedure
• Warehousing the info or some other ETL would work but then wouldn't be live data

Recreating equivalent functionality off the tables directly was also considered but not something I want to do for many reasons.

Any other potential solutions I'm overlooking? Thanks!

Chris Francis

For anyone interested, I found a Function close enough to the Stored Procedure and made a View pulling in data from the various Functions. The View can then be queried with the read-only DB user.

A few things less efficient than I would have liked but it works.