John,

Try (this is a working version - used some help)

;with cte_t AS
 (
 SELECT T1.RightsName,
       T1.pk_rights,
       t1.fk_rights,
       0 AS LEVEL,

       Cast(null as varchar(max)) AS ParentName, 

       T2.RightsOrder,
       T2.Description AS ChildDescr
  FROM @Test T1
 INNER join @test T2 ON T1.pk_rights = T2.pk_rights
 WHERE T1.fk_rights IS NULL
 UNION all

 SELECT T1.RightsName,
       T1.pk_rights,

       T1.fk_rights,
       LEVEL + 1 AS LEVEL,

       D.RightsName AS ParentName,
       T1.RightsOrder,
       T1.Description AS ChildDescr
  FROM @Test T1

 INNER join cte_t D ON T1.fk_rights = D.pk_rights
)
SELECT * FROM cte_t ORDER BY fk_rights, RightsOrder, LEVEL

>Given the following sql data, how do I select this hierarchically to produce a list with the parents/children in the proper order (children ordered by rightsorder)?
>
>

>pk_rights   fk_rights   RightsName                                         RightsOrder Description
>----------- ----------- -------------------------------------------------- ----------- --------------------------------------------------
>1           NULL        Open Category Library                              0           Open Category Library
>18          NULL        Open Portfolio Footnote Library                    1           Open Portfolio Footnote Library
>2           1           Add Category Library                               0           Add Category Library
>3           1           Delete Category Library                            1           Delete Category Library
>11          2           Edit Category Library                              0           Edit Category Library
>7           2           Add Category                                       1           Add Category
>12          2           Add Category Language                              2           Add Category Language
>14          2           Add Category Caption                               3           Add Category Caption
>9           2           Delete Category                                    4           Delete Category
>16          2           Delete Category Caption                            5           Delete Category Caption
>17          2           Delete Cateogry Language                           6           Delete Cateogry Language
>8           7           Edit Category                                      0           Edit Category
>13          12          Edit Category Lanaguage                            0           Edit Category Lanaguage
>15          14          EditCategory Caption                               0           EditCategory Caption