- "Stannis Baratheon, Warrior of Light... your sword awaits you."
It is unknown what became of the sword after Stannis' death at Winterfell. It's possible that Ramsay took Lightbringer as a trophy after he found Stannis' body, like he claimed to have done in the novels.
In the books
In the A Song of Ice and Fire novels, Lightbringer is a sword of legend, forged and wielded by Azor Ahai, chosen by the Lord of Light to fight the darkness. The sword was said to glow and radiate heat, burning whoever touches it.
As in the TV series, Melisandre enchants Stannis's sword and dubs it Lightbringer, as she believes him to be Azor Ahai reborn. However, Melisandre is apparently unable to get the sword to radiate heat, and it only gives off bright light.
Thus there is a discrepancy between the "Lightbringer" of legend, and Stannis's sword "Lightbringer", named after the original.
In A Storm of Swords, Maester Aemon asks Stannis to see the sword. Stannis cannot understand how a blind man can see his sword, but shows it to him. Sam describes the sword in details to Aemon: "It glows as if it were on fire. There are no flames, but the steel is yellow and red and orange, all flashing and glimmering, like sunshine on water, but prettier. I wish you could see it, Maester". While they walk away, Aemon seems to be deep in thought. He asks Sam whether he felt any heat from the sword. Sam admits he saw lights, but did not feel any heat. Aemon does not reveal the purpose of his question (perhaps it is ironic that a blind man noticed what others with eyesight did not). It is possible that he felt it was not the real Lightbringer, and Stannis was not Azor Ahai, and the real Lightbringer is supposed to radiate heat.
In the taunting letter that Ramsay sends Jon, he claims that he destroyed Stannis and his host, and has possession of his magic sword. It is unknown if Ramsay indeed has Lightbringer, since there are reasons to believe the letter contents are false.