Hmm...to start, the old bridge that leads on to the island is huge and extremely well lit up. On a weekend night there are hundreds of people catching fish throughout the night, especially last weekend!
There is another public pier that is very well lighted on the back side of the island. I've been there, but for the life of me right now, I can't remember the name of the street. Check the grand isle tourist commission site. Oops I just saw valvguy1's post...I think he's right about Smith lane? He's right the water is not the best there but the small white trout can often be caught to keep most kids happy.
Also, I like to fish the lighted pier at Sand Dollar marina. Probably not supposed to fish there at night? We do and have never had a problem. My buddy went a few times last week and caught his limit. The fish did not turn on until 2.30am though.
Next I would say for you to stay at Ricky's motel. It's not the best, but there are excellent cooking and hanging out facilities there and a terrific lighted fishing pier.
Next I would say to get a small boat (I know of folks that use a kayak), cruise the back side of the island, and fish the numerous lighted private piers. Just be respectful and don't fish the piers where the owners are fishing at the time - they have put a lot of work into building the pier and especially do not attempt to get on any private pier at night!