Hmm, yes I did rather forget the explanation there, didn't I?
It's pretty straight-forward: every connection takes a special Shared lock at the database level, regardless of whether a query is executing or not. This is how SQL Server knows whether anyone is 'in' the database or not (some operations require no other sessions in the database). The join is just to check that the connection session belongs to a user, not the system itself.