I'm going to go with the people who said Morn. Even to the point of having his very own episode, and still not getting any lines.
Either that, or possibly the world's greatest bit part character, saw through 14 years of Star Trek, and moving from simple helmsman and the first person to pilot the Enterprise-D's battle bridge, all the way through Engineering to become Chief Engineer when Starfleet took over operating Deep Space Nine and have Captain Picard follow him to his favourite transporter room to personally bid him farewell - Miles O'Brien.
TOS: Mister Leslie, the only redshirt known to have died and come back to life. Or, he had a twin brother who coincidentally served on the same ship.
TNG: Lieutenant Hawk for all the reasons the OP stated. Also Kell Perim.
But I'd also like to mention Ensign Sito Jaxa. What an incredible character, what a great arc, what a lovely young woman, what a tragic end.
DS9: Enrique Muniz. Great guy and sadly missed.
Voyager: Kaidan Alenko. /jk
According to Beta, Sito wasn't killed on the mission, but captured by the Cardassians, and eventually repatriated to the Federation as part of a prisoner exchange
Thank you thread. I now know the name of that Trill boff from Insurrection.
I would place her on my list. As well as Morn, Nurse Ogawa and Hawk from First Contact. I was actually hoping Hawk was going to become a regular member. I really liked that guy.
Failing that, I really hoped we would at least see Kell Perim again.
*******************************************
A Romulan Strike Team, Missing Farmers and an ancient base on a Klingon Border world. But what connects them? Find out in my First Foundary mission: 'The Jeroan Farmer Escapade'