It Makes Critical Hits More Likely
That's it, really. The confirmation roll makes a critical hit less likely, because you have another chance to fail. How much less likely depends on the difficulty of the roll to hit the target (as to confirm you just need to hit).
If you need an 11 on your d20 confirmation roll to hit, then you're going to not get a critical hit around half of the time you threaten a critical hit. Removing that roll also removes the failure chance.
How Significant Is It?
It depends. If you need a natural 20 to critical hit, you've already only got a 5% chance to get that, and if you need an 11 to hit, you've only got a 50% chance even if that 5% chance happens. Removing the confirmation roll doubles your effective crit chance, back to 5%. That's honestly not a huge deal, although it does mean your PC characters will get hit by crits more often as well.
But if someone is using a Rapier (18-20/2 in 3.5) and puts Keen on it (15-20/2), they have a really wide threatened zone on the dice. That's a 30% chance to threaten a critical hit, assuming a 15 on the dice can hit. Removing the confirmation roll from that person will make an equal impact in terms of the percentage boost, but a 30% crit chance is a lot higher than 5% crit chance in practice.
An NPC with multiple attacks and a high crit chance like that that doesn't have to confirm his critical hits is more likely to be able to kill a PC in a single turn with a full attack, because he will crit multiple times more often.
What does more critical hits actually do to the game? The short answer is, it depends.
At very low levels when HP is scarce, a critical hit from a monster stands a decent chance of outright killing a PC. You will make that more likely. The impact as you gain levels is going to be more subdued, both because a single critical hit won't kill a PC and because monster attack rolls can get so high (while threat ranges tend to only be 20, particularly for natural attacks) that monsters who roll a threat rarely fail to confirm anyway.
The change benefits some people more than others. If you have a very high attack modifier and can hit on a 2, you will almost always confirm a crit roll anyway. If you need a 19 to hit, you very rarely will. Under this rule, one of those people is being helped significantly more than the other.
Another area of concern is touch attacks that can crit, including spells. A critical hit on a drain attack can be devastating. Shadows (a monster with a stat drain attack) have a fairly low to hit (+3), so even though they're attacking touch AC their confirmation odds are not that great. Given how much harder it is to raise touch AC in most cases, anything with a very low attack (like the Shadow) will benefit more from the change than something with a high attack. Things with a high touch attack roll are going to confirm most of the time anyway.
On the PC side of things, the impact depends on what your players are doing. A Rogue stacking sneak attack probably won't notice a lot of impact, as that damage isn't multiplied on a crit anyway (and their base damage is often unspectacular). Someone using a weapon like a Rapier will get a lot of crits, but once again may not have strong damage so it's not a big deal.
OTOH, I had a 30 strength, large size, greatsword wielding Cleric in my game recently. He did 3d6+17 damage before power attack. If he power attacks significantly and gets a crit, whatever he's hitting is having a bad day. That weapon has a threat range of 19-20, so it will come into play around 10% of the time. Making his crits easier to confirm is going to let him bring some pain.
Consider a case where he power attacks for his maximum. He was level 12, and with Divine Power has Fighter BAB of +12. He Power Attacks for 12 and is now doing 3d6+41. He then purges Surge of Fortune, which makes his next attack roll an automatic natural 20. He just hit something for pushing 100 damage with one attack, and there is no chance of failure because he doesn't have to confirm. He probably feels pretty awesome when he does that. You may find it less ideal.
There's also the case of someone who has a very low chance of hitting with an attack at all (say they need a 20 on the dice to hit). Normally, they can score a hit with a 20, but only have a 5% chance of confirming a critical. Under the modified rule, every single attack that actually lands will be a critical. That is a pretty weird outcome, but it may not come up very often in your game.
In general, monsters in particular the overwhelming majority of the time only threaten a critical on a 20. Assuming they need to roll an 11 to confirm (which depending on the monster can be a bad assumption), your rule change will only alter the outcome on half of their natural 20 rolls. That means approximately 2.5% of the time, they'd get a crit when they otherwise would have only gotten a hit.
While that's not nothing in terms of boosting the lethality of the game, it's also far from catastrophic.
All of that assumes your players don't take steps to get defense or immunity against critical hits. If they do that, then they don't have to worry about the change at all and it only helps them.
The Fun Factor
I think you have a pretty good point in what you noticed: rolling a natural 20 and then not critting is anti-climatic. That big roll at a key situation is great fun, until you roll a 2 on the confirmation. Then it feels like a lost opportunity.
Slightly Tweaked Idea - No Confirm on Natural 20 Only
What you may want to consider is making it so that a natural 20 is an automatic critical, no confirmation. Any other threatened critical has to be confirmed as usual. That will boost the special feeling and excitement of a natural 20 (no letdown!), but won't alter the balance of weapons with very large crit ranges (they'll have to confirm as usual and keep the same extra chance they're getting currently, as a result).