CRM Read-only DB user - Stored procedure/functions

Chris Francis
Chris Francis
Facilitator 1
in Blackbaud CRM Developer Discussions

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!

Answers

  • Chris Francis
    Chris Francis
    Facilitator 1

    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.

Categories