It definitely depends on the game. Some allow second (or third or fourth) players to play as "guests" (not signed in to any particular account), and some don't. Party games, or games designed primarily for co-op, are definitely more likely to allow guests.

Weirdly, in my limited experience it seems that 'premium' / AAA games are more likely to require an account - I suspect it's because they want to be able to save unlocks/progress for those players. Perhaps they think it will incentivize those players to buy a copy of their own.