For intelligence gathering these units are extremely useful. The Bruneval raid to get a German Würtzburg radar was highly useful. The LRDG made a valuable contribution to Britain's North African campaign. There are lots of examples of this sort available.
Where they failed generally was in contributing to a massive or even significant level of shift in operational or strategic position within a theater or campaign. The sole success I can point to right off would be Eben Emael in Belgium. The SAS was annoying, little more.
The US Navy's WW 2 frogmen were more engineering specialists and reconnissance troops than combat units. Their primary mission was removing beach obstacles and plotting the position of such things along with enemy beach defenses. They might also be tasked with collecting soil samples and survey work on the beach to determine its suitability for landings.
Today's SEAL and EOD units in the US Navy have much the same missions. The SAS, Delta Force, etc., have inherited the specialist reconnissance and micro combat mission where the target is very specific and otherwise not capable of neutralization. These guys don't win wars on their own. Rather they make valuable contributions on a very narrow and specific level with their greatest contribution being intelligence.

__________________
Truth is stranger than bullshit!