Firstly, perhaps sulphur is the problem. Sulphur is one of the elements of the periodic table and is a building block encorporated into lots of chemicals. Many compounds containing sulphur have an unpleasant smell - for example, hydrogen sulfide gives off the aroma of rotten eggs and the stench associated with garlic and skunks is also due to sulphur-containing compounds. Sulphur is also essential for life as its found in two amino acids (the building blocks of proteins), known as cysteine and methionine. As it happens, asparagus contains both of these amino acids.
When not required by the body, cysteine and methionine are broken down into a number of products that can be excreted in urine. Could these be the stinky compounds we are smelling? Anecdotal evidence suggests that taking the amino acid methionine as a supplement can result in the same flavored wee as when asparagus is eaten, further supporting the hypothesis that it is sulphur containing amino acids that are responsible for the smell.
Another theory is that a breakdown product of another amino acid called asparagine (asparagine-amino-succinic-acid monoamide) in the urine is responsible for the foul smell of asparagus wee. Some people don't actually make the enzymes required to make this particular breakdown product and this could account for the fact that some people's wee just doesn't seem to stink, how ever much asparagus they eat. Asparagine was in fact first isolated from asparagus, where it is found in really high concentrations. It is known as a non-essential amino acid, meaning that we can synthesize it in the body and do not need to consume it. So if the smell bothers you too much you'll do just fine if you never eat asparagus again.
But not everyone agrees with this theory. It also happens that the emergence of asparagus cabbage wee coincided with the introduction of sulfur containing fertilizers for asparagus in the 17th century. Some believe that at least three major sulphur containing components are responsible for the pungent odor of asparagus wee. Two of these are S-methylthioacrylate and its methanethiol addition product, S-methyl-3-(methylthio)thiopropionate in case you're curious (Methanethiol is the stuff that gives rotten eggs their distinctive bouquet by the way).
Research into this phenomenon has also revealed that a subset of the population cannot smell asparagus cabbage wee, even at its very worst. Lucky them, I say.