From Wisegeek.com:
Scientists are still not entirely sure which set of chemicals or
amino acids contained in asparagus actually cause the smelly pee. The stalks themselves do not acquire a similar odor as they are prepared, so whatever happens most likely happens after ingestion. Experts believe that those with a certain gene produce a
digestive enzyme which breaks down the asparagus into various
amino acids. One of those compounds is called methyl mercaptan, which is the same chemical which gives a skunk its defensive smell. One
theory suggests that asparagus breaks down quickly in the body and an
enzyme releases methyl mercaptan, which eventually goes through the
kidneys and is excreted as a waste product in the urine.
Others suggest that the asparagus smell is created by other
amino acid compounds called thioesters. There is also an
amino acid called asparagusic acid, which is not surprisingly found primarily in asparagus. If these compounds are broken down and mixed with the genetically-created enzyme, the results could be a strong smelling urine. This smell is actually considered to be good news, since it proves that the asparagus eater's kidneys are functioning as they should.