Recursive CTE Multiple Levels
As CTE's are for the first test quite difficult to understand, i wrote a approach, with all Levels at the end
The Option at the end is nit needed, but to reduce espeically when you are trying to figure out what happens, you shoukld add it at a low number
WITH MyTest as
(
  SELECT P.DBName, P.Privilege,p.PrivilegeType,P.PrivilegeDetail,P.TableName , CAST(P.PrivilegeDetail AS VarChar(Max)) as Level
  FROM SQLTest P
  WHERE P.Privilege = 'Default_Role'

  UNION ALL

  SELECT P1.DBName, P1.Privilege,p1.PrivilegeType,P1.PrivilegeDetail,p1.TableName , CAST(P1.PrivilegeDetail AS VarChar(Max)) + ', ' + M.Level
  FROM SQLTest P1  
  INNER JOIN MyTest M
  ON M.PrivilegeDetail = P1.Privilege
 )
SELECT * From MyTest

OPTION (MAXRECURSION 50);

REsult
DBName  Privilege       PrivilegeType   PrivilegeDetail     TableName   Level
TSDB    Default_Role    Role            App_Role            (null)       App_Role
TSDB    App_Role        Role            Security_Role       (null)       Security_Role, App_Role
TSDB    Security_Role   Table           Customers           Customers    Customers, Security_Role, App_Role
TSDB    Security_Role   Table           Sales               Sales        Sales, Security_Role, App_Role
TSDB    Security_Role   Table           Users                Users       Users, Security_Role, App_Role