USP_QUEUEPROCESS_APPUSER_GRANTED_ALL_STEPS

        create procedure dbo.[USP_QUEUEPROCESS_APPUSER_GRANTED_ALL_STEPS]
        (
            @CURRENTAPPUSERID uniqueidentifier,
            @STEPLISTXML xml,
            @GRANTED bit output
        )
        as 
            begin
                set nocount on;

            if dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1
              set @GRANTED = 1
            else
              begin
                    declare @STEPSTABLE table ([BUSINESSPROCESSCATALOGID] uniqueidentifier, [PARAMETERSETID] uniqueidentifier);

                    -- unwrap the steps
                    insert into @STEPSTABLE ([BUSINESSPROCESSCATALOGID], [PARAMETERSETID])
                    select 
                        T.c.value('(BUSINESSPROCESSCATALOGID)[1]', 'uniqueidentifier') as 'BUSINESSPROCESSCATALOGID',
                        T.c.value('(PARAMETERSETID)[1]', 'uniqueidentifier') as 'PARAMETERSETID'
                      from @STEPLISTXML.nodes('/STEPS/ITEM') T(c);

                    -- check any steps that involve the KPI instance, mailing, or mailing export record types
                    declare CHECK_CURSOR cursor local fast_forward for
                    select 
                          S.[PARAMETERSETID],
                          C.[PARAMETERTABLENAME],
                          upper(R.[NAME]) as [RECORDTYPE],
                    I.[ID] as [INSTANCEID]
                      from @STEPSTABLE S
                      inner join dbo.[BUSINESSPROCESSCATALOG] C on C.[ID] = S.[BUSINESSPROCESSCATALOGID]
                      left outer join dbo.[RECORDTYPE] R on R.[ID] = C.[RECORDTYPEID]
                  left outer join dbo.[BUSINESSPROCESSINSTANCE] I on I.[BUSINESSPROCESSPARAMETERSETID] = S.[PARAMETERSETID]
                     where upper(R.[NAME]) = 'KPI INSTANCE'
                          or upper(R.[NAME]) = 'MAILING'
                          or upper(R.[NAME]) = 'MAILING EXPORT'
                    or I.[ID] is not null;

                    declare @PARAMETERSETID uniqueidentifier;
                    declare @PARAMETERTABLENAME nvarchar(100);
                    declare @RECORDTYPE nvarchar(50);
                declare @INSTANCEID uniqueidentifier;

                    declare @SQL nvarchar(max);

                    set @GRANTED = 1;

                    open CHECK_CURSOR;

                    fetch next from CHECK_CURSOR into @PARAMETERSETID, @PARAMETERTABLENAME, @RECORDTYPE, @INSTANCEID;

                    while @@FETCH_STATUS = 0 
                    begin

                      if @RECORDTYPE = 'KPI INSTANCE'
                        -- @PARAMETERSETID = KPIINSTANCE.ID
                        set @GRANTED = dbo.[UFN_SECURITY_APPUSER_GRANTED_KPIINSTANCE](@CURRENTAPPUSERID, @PARAMETERSETID);
                      else
                          -- mailing, mailing export
                          if @RECORDTYPE = 'MAILING' or @RECORDTYPE = 'MAILING EXPORT'
                            begin
                                -- @PARAMETERSETID gets you from the parameter table to MKTSEGMENTATION,
                                -- since all of the parameter tables have SEGMENTATIONID in them
                                set @SQL = 'select @GRANTED = dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID])' + char(13) +
                                               '  from dbo.[' + @PARAMETERTABLENAME + ']' + char(13) +
                                               '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [' + @PARAMETERTABLENAME + '].[SEGMENTATIONID]' + char(13) +
                                               ' where [' + @PARAMETERTABLENAME + '].[ID] = @PARAMETERSETID';

                                exec sp_executesql @SQL, N'@GRANTED bit output, @CURRENTAPPUSERID uniqueidentifier, @PARAMETERSETID uniqueidentifier', @GRANTED = @GRANTED output, @CURRENTAPPUSERID = @CURRENTAPPUSERID, @PARAMETERSETID = @PARAMETERSETID;
                            end
                    else
                      if @INSTANCEID is not null
                        set @GRANTED = dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, @INSTANCEID)

                      if @GRANTED = 0 break;

                      fetch next from CHECK_CURSOR into @PARAMETERSETID, @PARAMETERTABLENAME, @RECORDTYPE, @INSTANCEID;
                    end

                    close CHECK_CURSOR;

                    deallocate CHECK_CURSOR;
                end;
          end;