I am working with SQL Server 2022 Master Data Services, and need to deploy some custom SQL scripts to include in business rules. I have created some functions and some stored procedures, all in the usr schema.
The functions that I have created are visible in the web UI, and can be used as the condition for a business rule, but the stored procedures are not visible in the Then or Else section - the "User defined scripts" section of the drop-down is empty.
I found a suggestion that I need to grant EXECUTE authority on the usr schema to mds_br_user, but that user is already the owner of the schema. There is an old MDS database where the procedures ARE visible in the business rules, but I can't find any differences in the permissions.
The procedure has been created with the input parameters defined in the docs:
CREATE PROCEDURE [usr].[myProc]
(@MemberIDList mdm.[MemberId] READONLY
,@ModelName NVARCHAR(MAX)
,@VersionName NVARCHAR(MAX)
,@EntityName NVARCHAR(MAX)
,@BusinessRuleName NVARCHAR(MAX)
)
What changes do I need to make to let me use my stored procedures in business rules?