Query data in a BOM hierarchy
As you mentioned you can use a CTE, actually a Recursive CTE, to do what's known in the industry as a BOM Explosion.
If I understand your logic correctly, this is essentially the query of the Recursive CTE you'd want to use.
WITH CTE_Recursive AS -- Recursive CTE to explode the BOM
(
    -- Base Case
    SELECT ID AS ChildId, Parent_ID AS ParentId, [Level], [User]
    FROM BOM
    WHERE [User] = 5678-- Filter on the User to start with here

    UNION ALL -- This allows us to call the CTE recursively

    -- Recursive Case
    SELECT R.Parent_ID AS ChildId, B.Parent_ID AS ParentId, R.[Level] - 1 AS [Level], B.[User] -- Notice how the Parent of the previous call becomes the Child here, as it recursively works its way up the tree (and the new Parent comes from the BOM table, and is the Parent of the previous Parent).
    FROM CTE_Recursive AS R
    INNER JOIN BOM AS B
        ON R.ParentId = B.ID
)

-- Final SELECT for the results
SELECT ChildId, ParentId, [Level]
FROM CTE_Recursive

Let me know how this works out as I don't have any data to test it with at the moment, and you basically want a reverse BOM explosion, since you need to filter on a specific child and get all ancestors. (A regular BOM explosion is what I'm more used to writing recursive CTEs for, where you start with a specific ancestor and get all children.) Also please see the link I provided above for more examples and information on using recursive CTEs.