Recursive cte sql with for hierarchy level

Question

I have a little problem with this recursive CTE, it works fine except when I have a user without root readable rights means no entry for this element. So if I run this query on a user with rights just on the leaves inside the tree the level part of this query won't work correctly.

It will show the real level hierarchy for example 6 but its the top first readable element for him so it should be 1.

WITH Tree
AS (
SELECT
    id,
    parent,
    0 AS Level,
    id AS Root,
    CAST(id AS VARCHAR(MAX)) AS Sort,
    user_id
FROM SourceTable
WHERE parent IS NULL

UNION ALL

SELECT 
    st.id,
    st.parent,
    Level + 1 AS Level,
    st.parent AS Root,
    uh.sort + '/' + CAST(st.id AS VARCHAR(20)) AS Sort,
    st.user_id
FROM SourceTable AS st
    JOIN Tree uh ON uh.id = st.parent    
)

SELECT * FROM Tree AS t
    JOIN UserTable AS ut ON  ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934'
ORDER BY Sort

the level is as follows

id  level
 5    0
 2    1
 7    2
 4    2
 1    2
 6    1
 3    2
 8    2
 9    3

When a user now just have read rights to id 8 and 9 the level from CTE stays at 2 for id 8 and 3 for id 9 but I need for id 8 level 1 if there is no one before

Solution

You haven't told us how you know whether a user has rights to a given id. That is a necessary piece of information. I'm going to put some code below that assumes you add a column to your query called hasRights and that this column will have a zero value if the user does not have rights and a value of one if they do. You may need to tweak this, since I have no data to test with but hopefully it will get you close.

Basically, the query is altered to only add 1 to the level if the user has rights. It also only adds to the sort path if the user has rights, otherwise an empty string is appended. So, if ids 8 and 9 are the only items the user has access to, you should see levels of 1 and 2 and sort paths similar to '5/8/9' rather than '5/6/8/9'. If you still aren't able to get it working, it would help us tremendously if you posted a sample schema on SqlFiddle.

WITH Tree
AS (
SELECT
    id,
    parent,
    0 AS Level,
    id AS Root,
    hasRights AS HasRights,
    CAST(id AS VARCHAR(MAX)) AS Sort,
    user_id
FROM SourceTable
WHERE parent IS NULL

UNION ALL

SELECT 
    st.id,
    st.parent,
    Level + st.hasRights AS Level,
    st.parent AS Root,
    st.hasRights AS HasRights,
    uh.sort + CASE st.hasRights WHEN 0 THEN '' ELSE '/' + CAST(st.id AS VARCHAR(20)) END AS Sort,
    st.user_id
FROM SourceTable AS st
    JOIN Tree uh ON uh.id = st.parent    
)

SELECT * FROM Tree AS t
    JOIN UserTable AS ut ON  ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934'
ORDER BY Sort