How to tighten threaded pipe in an existing assembly of pipes
You either disassemble it from its closest union, or start cutting. 

If it leads to a pump, I'd advise installing unions at both ends of it, to facilitate replacements.

Simply tightening this pipe probably isn't the way to go. It's likely deteriorated at the threads and that's why it's leaking. You'll risk snapping it off in the fitting to get it watertight again, unless you take it apart, inspect the threads, and re-dope it.

Also, are you sure that's brass and not galvanized steel? If it is steel, where's your dielectric fitting? (that's why it's leaking). Even if it is brass, it's crappy: look at the color of that 2" nipple below it; that's made out of real brass.

You mentioned sweat fittings. Can this be taken back all the way to the first sweated fitting, so you could do away with as many (threaded) connections as possible?