Paul Tibbets, the captain of the B-29 that dropped the bomb on Hiroshima, the Enola Gay, definitely knew what they were dropping. He was in charge of figuring out, in essence, how to drop it successfully (and in this role, he picked his own men, helped design the modified B-29s, and did a number of other key mission-relevant details).

Charles Sweeney, the captain of Bockscar, the plane that would drop the second bomb on Nagasaki, was told about the payload the minute he was brought into Tibbet's project, very early on.

Of course, both of them knew what there was to be known at a given point in time. So they didn't know the exact yields of the bombs until after the Trinity test, for example. But they understood, and worked with scientists who understood, the important aspects of it.

As for the others on the planes — it's not clear they had a sense of it until the Hiroshima mission. (The Nagasaki mission, of course, knew about the success of the Hiroshima mission, and its payload, before they went out.) They knew they were part of a top secret detail whose purpose was not yet fully revealed to them. They knew that they'd be dropping physically large bombs of some sort — they practiced with "Pumpkins," which were the size of the Fat Man bomb, but just filled with conventional explosives. And there were a lot of scientists involved. So it's clear that some of them put two-and-two together, as the idea of atomic bombs was something known popularly before the war.

As the Enola Gay was flying to its target, the tail-gunner, Robert Caron, asked Tibbets if what they were carrying was "a chemist's nightmare" (chemical weapons). Tibbets said no. Then "a physicist's nightmare" — Tibbets hedged ("not exactly"). Then, finally, he said, "Colonel, are we splitting atoms today?" And Tibbets gave him a "funny look" and confirmed it. Caron said it was a "lucky guess." After this, Tibbets told the entire crew — en route — as he put it, "the last piece of the puzzle," that they were carrying an atomic bomb.

(The above account is from Richard Rhodes, The Making of the Atomic Bomb, p. 707).

Which is to say — it's clear that someone like Caron, a tail-gunner, didn't know and wasn't supposed to know. And that Tibbets did. Did the bombardier know? It's hard to imagine him being able to do his job well without some information... but maybe they gave him just enough to put the thing in the right place, not to know what it really was. I don't know for every member of the crews.

My guess would be the captains who Tibbets thought would actually possibly fly the bombs all knew, but most everyone else in the 509th would have learned after Hiroshima, if they were not on the Enola Gay. But I have never seen a systematic compilation (it is not that easy to do; there was no "list" of who knew and who didn't, so you have to rely on biographies and interviews).

Most of the rest of the military had no clue, other than the highest levels of it.