Get value of two instances of a secondary table
It looks like you are just trying to pivot some data.

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then p.Type else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then p.Type else null end)
  --, [Players Permission]=max(case when p.Name='Players' then p.Type else null end)
  from user u 
    inner join Permission p ON p.UserId = u.Id
  group by u.Id, u.username;


or if we want the name of the type for type integer, something like:

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then t.Name else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then t.Name else null end)
  --, [Players Permission]=max(case when p.Name='Players' then t.Name else null end)
  from user u 
    inner join Permission p ON p.UserId = u.Id
    inner join [Type] t ON p.Type = t.Id
  group by u.Id, u.username;



If your nonclustered index on permissions is (Id, UserId, Name), it's not doing you any favors. unique nonclustered (UserId, Name, Type) or nonclustered (UserId, Name) include (Type) would be more useful.