Iterative query with permissions

There I found a way that I do not know if it is the best possible, but it works well:

SET NOCOUNT ON 
-- Tabla con el resultado final
DECLARE @TablaFiltrada TABLE (
    ID INT,
    ID_PADRE INT,
    DESCRIPCION NVARCHAR(200),
    R BIT
)
-- Declaro la tabla que va a almacenar los ID de nodos que no tienen permisos de lectura
DECLARE @TablaDenegados TABLE (
    ID INT,
    ID_PADRE INT
)

DECLARE @Rows INT, @IdOperador INT, @EsSuperAdmin BIT

SET @IdOperador = :idOperador
SET @EsSuperAdmin = (SELECT ES_SUPERADMIN FROM V_SYS_OPERADOR WHERE ID_OPERADOR = @IdOperador)

-- Cargo los nodos encontrados
INSERT INTO @TablaFiltrada (ID, ID_PADRE, DESCRIPCION, R)
SELECT J1.ID, ID_PADRE, DESCRIPCION, P.R,
FROM jerarquia J1
    LEFT JOIN (SELECT * FROM PERMISOS WHERE ID_OPERADOR = @IdOperador) P ON J1.ID = P.ID_NODO
    WHERE DESCRIPCION LIKE :palabrasClaves AND (R IS NULL OR R = 1)

SET @Rows = @@ROWCOUNT

WHILE @Rows <> 0
BEGIN

    INSERT INTO @TablaFiltrada (ID, ID_PADRE, DESCRIPCION, R)
    SELECT P2.ID, P2.ID_PADRE, P2.DESCRIPCION, P.R
    FROM jerarquia P2
        INNER JOIN @TablaFiltrada P1 ON P2.ID = P1.ID_PADRE
        LEFT JOIN @TablaFiltrada P3 ON P3.ID = P2.ID
        LEFT JOIN (SELECT * FROM PERMISOS WHERE ID_OPERADOR = @IdOperador) P ON P2.ID = P.ID_NODO
    WHERE P3.ID IS NULL

    SET @Rows = @@ROWCOUNT -- Sigo iterando

END

-- Cargo los nodos con permisos de lectura denegada
INSERT INTO @TablaDenegados
SELECT DISTINCT(ID), ID_PADRE FROM @TablaFiltrada WHERE @EsSuperAdmin = 0 AND (NIVEL_NODO >=2 AND R = 0)

SET @Rows = (SELECT COUNT(*) FROM @TablaDenegados)

-- Almaceno toda la rama en la Tabla de denegados
WHILE @Rows <> 0
BEGIN

    INSERT INTO @TablaDenegados (P2.ID, P2.ID_PADRE)
    SELECT  P2.ID, P2.ID_PADRE
        FROM    @TablaDenegados P1
        INNER JOIN @TablaFiltrada P2
            ON P2.ID_PADRE = P1.ID
        LEFT JOIN @TablaDenegados P3
            ON P3.ID = P2.ID
    WHERE P3.ID IS NULL

    SET @Rows = @@ROWCOUNT -- Sigo iterando

END

-- Selecciono los que finalmente tienen los permisos adecuados
SELECT DISTINCT * FROM @TablaFiltrada
WHERE ID NOT IN (SELECT ID FROM @TablaDenegados)
ORDER BY DESCRIPCION

If you doubt, as Lamak says it is an interesting question, I tell you a way to solve it, maybe it is not the most optimal and has some limitations:

• Each "level" can have up to 9999 elements (this can be modified eventually)
• It can handle a maximum number of branches of the length of LEN(VARCHAR(MAX)) / 4

Suppose we have a complete Hierarchy like the following:

ID      DESCRIPCION
======= ============================
1,00    Accesorios para celulares
3,00        Baterías
4,00            Genericas
5,00            Originales
6,00        Protectores
7,00            Plásticos
8,00            Vidrio
2,00    Celulares
9,00        Gama Alta
10,00           Iphone 6
11,00           Samsung S8
12,00       Gama Media
13,00   Accesorios PC
14,00       Teclados
15,00       Monitores

It is a silly example of categories to classify a product, now suppose we want to "disable" the entire branch of "Cellular", ie "Cellular", "High Range", "Iphone 6", etc. but disabling only the "Cellular" level.

DECLARE  @TablaFiltrada TABLE 
(
    ID      INT,
    NODO            VARCHAR(MAX)
)

DECLARE @Jerarquia TABLE (
        ID              INT,
        ID_PADRE        INT,
    DESCRIPCION     NVARCHAR(200)
)

DECLARE @Permisos TABLE (
    ID              INT,
    R           BIT
)

INSERT INTO @Jerarquia(ID, ID_PADRE, DESCRIPCION)
VALUES ( 1, NULL, 'Accesorios para celulares'),
       ( 2, NULL, 'Celulares'),
       ( 3,    1, 'Baterías'),
       ( 4,    3, 'Genericas'),
       ( 5,    3, 'Originales'),
       ( 6,    1, 'Protectores'),
       ( 7,    6, 'Plásticos'),
       ( 8,    6, 'Vidrio'),
       ( 9,    2, 'Gama Alta'),
       (10,    9, 'Iphone 6'),
       (11,    9, 'Samsung S8'),
       (12,    2, 'Gama Media'),
       (13, NULL, 'Accesorios PC'),
       (14,   13, 'Teclados'),
       (15,   13, 'Monitores')

-- SOLO "Celulares" TIENE EL FLAG APAGADO
INSERT INTO @Permisos (Id, R)
    VALUES (2, 0)

SET NOCOUNT ON 
DECLARE @Rows INT

INSERT INTO  @TablaFiltrada (ID, nodo)
SELECT  ID,
    RIGHT('0000' + CONVERT(VARCHAR, ID),4)
    FROM @Jerarquia
    WHERE ID_PADRE IS NULL

SELECT @Rows = @@ROWCOUNT
WHILE @Rows <> 0
BEGIN

    INSERT INTO  @TablaFiltrada (ID, nodo)
    SELECT  J.ID,
        N.Nodo + RIGHT('0000' + CONVERT(VARCHAR, J.ID),4)
        FROM @Jerarquia J
        INNER JOIN @Jerarquia P
            ON P.ID = J.ID_PADRE
        INNER JOIN  @TablaFiltrada N
            ON J.ID_PADRE = N.ID
        WHERE J.ID NOT IN (SELECT ID FROM  @TablaFiltrada)

    SELECT @Rows = @@ROWCOUNT -- Sigo iterando

END

SELECT  J.ID,
    SPACE(LEN(N.NODO)-4) + J.DESCRIPCION
    -- CASE WHEN NP.NODO IS NULL THEN 1 ELSE 0 END AS 'R'
    FROM  @TablaFiltrada N
    INNER JOIN @Jerarquia J
        ON N.ID = J.ID
    LEFT JOIN (SELECT N1.nodo
            FROM  @TablaFiltrada N1
            INNER JOIN @Permisos P
                ON P.ID = N1.ID
            WHERE P.R = 0
        ) NP
        ON LEFT(N.nodo, LEN(NP.nodo)) = NP.nodo
    WHERE NP.NODO IS NULL
    ORDER BY N.NODO

The exit:

ID      DESCRIPCION
======= ============================
1,00    Accesorios para celulares
3,00        Baterías
4,00            Genericas
5,00            Originales
6,00        Protectores
7,00            Plásticos
8,00            Vidrio
13,00   Accesorios PC
14,00       Teclados
15,00       Monitores

The essence of this method is to generate a field nodo that is the hierarchical representation of the "tree", something like this:

ID      nodo
======= ============================
1,00    0001
3,00    00010003
4,00    000100030004
5,00    000100030005
6,00    00010006
7,00    000100060007
8,00    000100060008
2,00    0002
9,00    00020009
10,00   000200090010
11,00   000200090011
12,00   00020012
13,00   0013
14,00   00130014
15,00   00130015

Thus it is more clear that if we only have the ID=2 as disabled only by updating the records whose LEFT(nodo,LEN(nodo)) = '0002' will be disabling the entire branch. The way to create the nodes can be modified, I personally like to see it because it is useful for ordering, but you could create the nodes by concatenating each id , for example 2|9|11 and we would have more flexibility in terms of amount of these.