UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE

CREATE function [dbo].[UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE]
(@APPUSERID uniqueidentifier,@PAGEID uniqueidentifier)
returns bit
as
/*
Returns true if the user has been granted and not denied the page expression form for a System Role.  Also returns true if the page does not have an expression form defined.
Returns false for non-admins if the page does not exist.
*/
begin

    declare @grant bit;
    declare @pageExpFormID nvarchar(36);
    declare @noSecurityRequired bit;
    declare @IsSysAdmin bit;

    select @IsSysAdmin = ISSYSADMIN 
        from dbo.APPUSER 
        where ID = @APPUSERID;

    if @IsSysAdmin = 1
        set @grant = 1;
    else
        begin
            select @pageExpFormID=
                coalesce(
                        PAGEDEFINITIONSPEC.value(
                            '
                            declare namespace bbspec="bb_appfx_pagedefinition";
                            declare namespace c="bb_appfx_commontypes";
                            /bbspec:PageDefinitionSpec[1]/@ExpressionDataFormID
                            '
                            ,'nvarchar(36)')
                        ,'00000000-0000-0000-0000-000000000000')
            from dbo.PAGEDEFINITIONCATALOG where ID = @PAGEID;

            if @pageExpFormID <> '00000000-0000-0000-0000-000000000000'
                begin

                    select @noSecurityRequired = 
                        coalesce(TEMPLATESPECXML.value(
                        'declare namespace bbspec="bb_appfx_viewdataformtemplate";
                         declare namespace c="bb_appfx_commontypes";
                         /bbspec:ViewDataFormTemplateSpec[1]/@NoSecurityRequired'
                        , 'bit')
                        , 0)
                    from dbo.DATAFORMTEMPLATECATALOG t inner join
                        dbo.DATAFORMINSTANCECATALOG d on t.ID = d.DATAFORMTEMPLATECATALOGID
                    where d.ID = @pageExpFormID;

                    if @noSecurityRequired = 1
                        set @grant=1;
                    else
                        begin
                            --If at least one grant and no deny then return true

                            --otherwise, false

                            set @grant=0;

                            --order by GRANTORDENY, deny will be first.


                            select top 1 @grant=GRANTORDENY

                            from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM

                            where APPUSERID = @APPUSERID
                            and DATAFORMINSTANCECATALOGID=@pageExpFormID

                            order by GRANTORDENY ASC;
                        end;
                end;

            else
                begin
                    if @pageExpFormID is null
                        -- page doesn't exist

                        set @grant = 0;
                    else
                        set @grant = 1;
                end
        end

    return @grant;

end