UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE

create function dbo.UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE
(
      @APPUSERID uniqueidentifier,
      @SELECTIONID uniqueidentifier
)
returns bit
as
/*
Returns true if user has been granted and not denied the selection for a System Role.
*/
begin
      --If at least one grant and no deny then return true
      --otherwise, false
      declare @grant bit;
      set @grant=0;

      -- note that this routine assumes the check for SysAdmin has been performed already

      -- check to see if the user is the ower of the selection, or the selection has been granted to everyone
      declare @OWNERID uniqueidentifier;
      declare @SECURITYLEVEL tinyint;
      set @SECURITYLEVEL = 0;
      select @OWNERID = OWNERID, @SECURITYLEVEL = SECURITYLEVEL from dbo.IDSETREGISTER where ID = @SELECTIONID;
      if (@OWNERID = @APPUSERID) or (@SECURITYLEVEL = 0)
            set @grant = 1;   

      -- user isn't the owner, and the selection has not been granted to everyone; check to see if the user has been 
      -- granted (and not denied) explicit rights to the selection.
      if @grant = 0
            begin
                  --order by GRANTORDENY, deny will be first.
                  select top 1 @grant = GRANTORDENY
                  from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SELECTION
                  where (APPUSERID = @APPUSERID) and (SELECTIONID = @SELECTIONID)
                  order by GRANTORDENY asc;
            end;

      return @grant;
end