[Mushiro]

All human souls return to "Mu" or nothingness. It's where Human souls come from, and where human souls return to. Shinigami were never humans, they're a different species all together, residing in a separate dimension from Earth.

So the answer to your question is; No, they don't turn into Shinigami, they die, and that is all.

There was actually a fan fic alternate ending (Very well drawn and written) where Light goes to the Shinigami realm. However, instead of becoming a Shinigami himself, he is forced to die in the same manner that he killed each person's whose name he wrote in the Death Note. So basically he just keeps dying over and over again. At the end, he asks Ryuuk how this is possible. Ryuuk says "The only Notebook that can make a Human die more than once is the Notebook belonging to the Great Shinigami God." So Light asks Ryuuk to take him to the Great Shinigami, so that he can make a deal with him so that he doesn't have to endure the punishment of dying over and over again. Light vows to make the Shinigami realm a better place to live, just as he tried with the Human Realm, the fan fic ends showing Light walking next to Ryuuk in the Shinigami realm on his way to see the Great Shinigami King.

Saw it on youtube, don't have the link though >_< I've seen it in other forums too. I think you can find it just by searching "Death Note Alternate Ending."