However I also know and as I saw someone on Google+ say the other day, "composition trumps resolution" (most of the time). Clearly there are pretty extreme examples where noise or unintentional lack of focus trashes the image, but in general this is absolutely true. In fact I posted an image on G+ the other day that shows this little factoid. I saw this stellar sunrise on my way to work and all I had with me was an iPhone4 and so stopped to take the shot. The shot was pretty cool and the composition very interesting despite being with an iPhone. I sure wished I had a DLSR with me though.
Now what is funny is most people who +1'd or commented on Google+ loved the image and only one noted the noise which was very obvious. I subsequently used a trial version of Topaz DeNoise and it did really improve the picture and pull much of the noise out. Classic case of technology didn't make the shot but it definitely improved it, but I do agree that the best camera is the best one you actually have with you.
This is the shot with the noise - click to enlarge the image and look at the clouds on the right blown up a bit and you will see the noise
Below is the shot after using Topaz DeNoise - click on the image again and the noise on the right is almost gone
Topaz DeNoise, Click here