It does depend on the context of the game and what kind of weapons the game gives you. In CoD4, two weapons works because of the way the game progresses, where you could be a completely different person from mission-to-mission, plus none of the weapons are really all that special anyway, being geared more towards real life practicality and dependability, so there's no sense of experimentation being lost there. But in a more linear sci-fi shooter with tons of crazy new weapons to experiment with, you are going to want to do some experimenting. But, if you can only carry two weapons, you're also going to want your arsenal to be as practical and flexible as possible most of the time, and any experimentation is usually too risky. This, of course, cuts into the immense fun I should be having in that kind of setting.
If developers absolutely have to limit your arsenal like that, I'd prefer they allow you to carry at least four weapons instead of just two, like what Fear 2 and Dead Space did, where my weapons usually include three practical weapons (something short-range, something medium-range, and something long-range), with the fourth slot being devoted to some sort of crazy-awesome "fuck you" weapon to use when I feel my enemies deserve it. It's the best possible compromise between forcing the player to carefully consider what weapons they want to have going forward and allowing the player to go nuts and feel like a badass, though it's still not quite as fun as carrying about a hundred laser canons on your back.