Button permissions for user forms

Buttons to execute stored procedures within a user form were added much later than the original design of the user forms editor and they don't have security to prevent users from running them.

As the user forms run custom code, clients can include additional logic to do a check itself and then either run the stored procedure code or return an error message to the user.  

An example below is provided.

If additional help is required please raise your request via the portal and our professional services team can assist.

CREATE OR ALTER PROCEDURE uspUserFormTest 
    @DummyInput VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @Username VARCHAR(100), --To locate and store user pressing button
        @Output VARCHAR(150) --To send message to user

    SELECT @Username = dbo.fnGetLoginName() --Get and store the login name of the user pressing the button

    --Confirm @Loginname has UPDATE permissions to user form before executing
    IF EXISTS(  
      SELECT *
      FROM dbo.pvConfigUserPermissions cup
      WHERE 
        cup.loginname = @Username         AND--Does the user pressing the button exist in the returned results from pvConfigUserPermissions
        cup.ResourceType    = 'UF'        AND--Keep as 'UF' meaning User Form         
        cup.Module          = 'DEB'       AND--Update to the Module the user form is in
        cup.Resource1       = 'DEBINTVW'  AND--Update to the name of the user form
        cup.Resource2       = ''          AND--Usually blank
        cup.Resource3       = ''          AND--Usually blank
        cup.SynDatabaseCode = 'Fin'       AND--Blank for non-finance, otherwise set to the finance entity code
        cup.UpdateFlag = 1 --Which permission to check for, e.g. the Update permission
    )
        BEGIN --Success.  User found to have permission
            --Update following two lines to run whatever is required, i.e. the code the Stored Procedure is currently running
            SELECT @Output = 'Stored Procedure dbo.uspUserFormTest executed correctly, with input parameter of: ' + @DummyInput--Storing a message to the end user
            RAISERROR(@Output,16,1)--Raising message to Synergetic
        END
    ELSE
        BEGIN--Fail.  User not found to have permission
            SELECT @Output = 'User ' + @Username + ' cannot run this stored procedure as you do not have Update permissions to the DEBINTVW form'--Update to inform user of permission issue
            RAISERROR(@Output,16,1)--Raises error message to Synergetic
        END
        
END
GO