There's a TNG novel, Contamination by John Vornholt, that's all about Worf and Troi conducting a murder investigation. It's a good exploration of that side of Worf's duties that we hardly ever saw onscreen.
I've long had a problem with the tendency to portray Starfleet security personnel just as soldiers, people who shoot and get shot at. Really, security should be more like police -- taking charge of potentially dangerous situations, trying to defuse them before they become violent. Security's job should be about protecting people and keeping the peace, not just standing by passively until the shooting starts. Or like the Secret Service. If the captain or first officer is leading an away team and things start to get dangerous, then the head security officer on the team should step forward and say, "All due respect, sir, but this is my responsibility now," and take charge of the situation until the threat is defused, while the rest of the security team spirits the command officers to safety.