The two main problems I have with bullet penetration are hit indicators, and ammunition.
I liked the bullet penetration aspect of Call of Duty 4, but agree with aussiesniper that the hit indicator could really throw things for a loop. In a map like Vacant someone picking a spot and blind firing with an M249, or in some maps the mounted machine guns (like in Overgrown) can just spray a house, if it a hit shows up they return to the spot with a few more bullets and odds are whatever hit is dead. Hit indicators could really fowl things up.
The other issue is ammunition. Most games it just isn't that big an issue, if worst comes to worst you can wing it with a pistol, the weapon from someone dead, or just die and respawn or wait for the next round and be back up to your full stores. This means that people can afford to be very, very generous with their ammunition and if there's bullet penetration there is often no reason to not clear an area not by entering it, but by putting a clip into it. There are areas of maps in Call of Duty 4 where I always stop and unload a good 10 to 15 rounds into before proceeding, even with the semi-automatic G3 I can afford to do this and unless someone hiding there is very lucky, they will catch at least one round and die. And if by some chance they don't die, follow up (although I have lost a fight through a wall before like this, player behind it had a fully automatic weapon and let loose a barrage after they took a non-lethal hit and killed me).
So at worst, bullet penetration can lead to a lot of "spray and pray" through walls, and with so much ammunition in most games as well as hit indicators, it can be a very effective tactic.
But bullet penetration can also lead to great moments too, hearing foot steps and firing a burst through the wall next to you and downing two guys (or being on the other side of the engagement and see a shower of tracers pass through the wall in front of you).
Overall, I like it, and I do still like the aspect of Call of Duty 4. But think it has to be carefully implemented.