In Blackbaud CRM there is a StoredProcedureSpec type that allows you to import a stored procedure into the CRM for use. Generally, other specs that call this will need to specify some kind of permission that allows them to execute it (such as GrantExecute). There are scenario's where that isn't enough however. Today I encountered the following error while trying to consume a stored procedure.
The EXECUTE permission was denied on the object 'USR_USP_....', database 'e4e-29e...', schema 'dbo'.
Come to learn, the StoredProcedureSpec also needed a "GrandServiceRolePermission" attribute set in it's XML metadata. Add the following attribute to the StoredProcedureSpec in question and the stored procedure should then be callable for other spec types (that have also setup their permissions correctly).
GrantServiceRolePermission ="true"