It depends on two things.

First, the song has to require, it and as others above have noted, some do. Water is Alright in Tay would never work without at least a little lapse into an accent. Same for Rocky Road to Dublin. Also, a lot of blues tunes require that the singer get the dialect and intonation right, Big Road Blues for instance. And You can't sing a cajun song, especially in its original french dialect without that accent.

Second, you have to be able to do the accent right. If the singer doesn't have the ear for the accent, or lays it on too thick it can sound phony, which is worse for the song than no accent at all.