Well apparently it works on Cod: W@W and Oblivion DLC according to Gravywave.
I know for certain it does not work with any Halo 3 map packs as my son couldn't even use the Halo 3 maps on the same xbox I downloaded them on, unless I was logged in as well. Most of my family doesn't play the same games that I do, so I don't know which DLC is coded which way.
All arcade games are coded to only work on the original 360 and on any 360 on which the purchaser is logged in.
I would assume more and more DLC will be coded similarly, since without the coding anyone can just do what Gravywave suggests and get "free" DLC.
So you will still "have" the DLC downloaded unless you delete all other associated files when you clear out the gamertag, but I wouldn't expect to be able to play much of that DLC.