Phrog hits the heart of the problem. The HS/HSC community has on occasion gotten to do some of the missions it trains to (and many want to do). 2515th, EMIO, HVBSS, SOF, and yes even CSAR. You just don't tend to hear about the actual missions that go down. The real problem is that Naval leadership doesn't want to actually use these aircraft in that manner, because as ADM Roughead and others like him have professed, the Navy doesn't want to risk losing them, and any mission over land or away from mom increases that risk in their eyes.
That being said, myself and others I know have gotten the opportunity to work with US and allied SOF units. Both training for and actually carrying out real missions. What I have seen is that usually a squadron will finally make the stars align to train together with a unit and get a foot in the door, and right when the relationship has been built and interoperability is going well, a change of command happens, usually at the CAG level. Suddenly the new boss decides that he doesn't actually know what a helicopter does or trains to do, so he decides to pull the plug because he doesn't want to risk an asset or just doesn't understand the missions and doesn't want to learn.
Just look at the ignorant, ass-covering shitshow that happens on a CVN when a -60 loads up with hellfire, M-197 or even .50cal, versus what happens when jets operate with ordinance.
So part of the problem is that 1. Big Navy doesn't actually want to risk losing a helicopter doing any of the missions we train to, and 2. CAG's just don't know enough and don't care enough to learn about helo capabilities, so they become extremely risk averse and thus we rarely get to carry out any actual missions.