You mean besides running the secret prisons? It's exactly the opposite for me; I can deal with the NSA overreaching on surveillance (so long as they occasionally get slapped back), but the CIA is a national embarrassment.
Take everything negative that you have ever heard about the CIA. Now consider that the victors write the history books and the CIA records have not yet been seized and poured over. Both those things considered together should paint a rather chilling picture.
Never underestimate the crimes of the CIA. We will likely never live to see the full extent of their atrocities.
OTOH, the CIA has some incentive to being known as ineffectual. If enemies underestimate them, the mission of the CIA will be easier.
OTOH, as far as I can tell, to call them ineffectual from ~1989 until ~2000s sometime would be excessively kind. So maybe they did also suck before that as well, but I think the end of the Cold War and political turnover after GHWB really gutted them, and it wasn't until they remade themselves as a black site operating, drone killing paramilitary group that they came back.
If the CIA has an incentive to be known as ineffectual, then I can only conclude that I am most likely assuming that they are incapable of far less than they actually are. The idea that I am underestimating their vile capacity only serves to disturb me further.
They have very strong institutional motives to be seen as highly-competent. A cynical view would be to think of intelligence agencies as semi-rational actors attempting to capture as much of the budget as possible with secrecy reducing all the costs of getting caught lying (think FEMA with no transparency and machine that prints get out of jail free cards).
The cynical view is never fully accurate, sometimes it isn't accurate at all because organisations always include idealist/true believers/patriots. Organisations which don't include idealists tend to weed themselves out pretty quickly and collapse.
Highly-competent in private/classified, highly incompetent in public, is the ideal balance. Although it does affect recruiting, so "highly constrained by morals and ethics" in public is probably better than generally incompetent.
It would be interesting to see what a "we are really professional, but generally law abiding" intelligence service could do.
Aside from running drones everywhere except Afghanistan itself (and even there I assume they run some). All those Pakistan, Yemen, etc. civilian casualties due to drones = CIA. And secret prisons, both gitmo and worse ("black sites", many of which are still unknown to the public.) Ongoing.
There are plenty of military assigned to NSA, to do things like operate SIGINT aircraft, or handle key distribution to the military on the COMSEC side.
NSA doesn't do much field intelligence stuff; it's mainly radio/etc. intercepts on military bases, aircraft, ships, etc. Their regular site security is either military or contracted out to Wackenhut or other private security companies, after all (which is apparently what one of Snowden's early jobs was).
The real tough guys of the signals world are the Army's ISA, who do tactical sigint and killing, all in one. I'm sure ISA and NSA cooperate a lot.
They're the guys who caught Pablo Escobar. Essentially the most secretive of the tier-1 JSOC units. IMO, the world's most awesome job for a bofh sysadmin -- you get to listen in to enemy communications, and use their improper security procedures to find them, and then punish them (by killing them).
Probably fiction. The NSA might use its black budget to fund tough guys (paying the payroll and overhead), but they'd probably be trained and managed by the CIA and / or military, as part of some joint operation.
It's very common for public officials to get too much money, and not be able to spend it. So they find another agency which wants the money, and organise some kind of joint operation. It keeps their spending nice and robust (so the bean counters don't realise they don't need all that money), and the other side gets the resources they need. It's win-win (for everyone but the taxpayers).
