UFN_SECURITY_APPUSER_GRANTED_TASK_IN_SYSTEMROLE

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_TASK_IN_SYSTEMROLE
(
@APPUSERID uniqueidentifier,@TASKID uniqueidentifier
)
returns bit

/*Returns 1 if the user has been granted the task in at least one role*/

as

begin

--Granted by role

if exists
    (
        select
            SRT.TASKID
        from
            dbo.SYSTEMROLETASK AS SRT
            inner join dbo.SYSTEMROLEAPPUSER AS SRAU on SRT.SYSTEMROLEID = SRAU.SYSTEMROLEID
        where
            SRT.TASKID = @TASKID
            and SRAU.APPUSERID = @APPUSERID
    )
    begin
        return 1;
    end

--Granted by feature group

else if exists
    (
        select 
            FEATUREGROUPFEATURE.FEATUREID TASKID
        from 
            dbo.SYSTEMROLEPERM_FEATUREGROUP
            inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEPERM_FEATUREGROUP.SYSTEMROLEID = SYSTEMROLEAPPUSER.SYSTEMROLEID
            inner join dbo.FEATUREGROUPFEATURE on SYSTEMROLEPERM_FEATUREGROUP.FEATUREGROUPCATALOGID = FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID
        where
            FEATUREGROUPFEATURE.FEATUREID = @TASKID
            and SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID
    )
    begin
        return 1;
    end

return 0;

end