Are there any musicians, especially in rock, jazz, folk, or country who are terrible in purely technical terms or unable to play in the proper sense, but manage to create inspiring or stunning music with their instruments?
You could say virtually every punk bank ever, especially The Ramones, who had to make up their own songs because they couldn’t play anyone else’s. And The Mekons, who eventually turned into a proto-alt-country act once they learnt to play, but recorded some of the messiest but most interesting punk (“Never Been In A Riot” being badly recorded and amateurish as hell, but highly acclaimed by critics such as Griel Marcus.)
Also there’s the much loved (by Zappa and others) late 60s/early 70s band The Shaggs who were almost entirely devoid of musical talent (their drummer seems to be playing to completely different songs, rhythms, etc.) yet managed to write and record a bunch of sweet, innocent, often funny and sometimes moving songs.
Jack Benny comes to mind. Although a very accomplished musician, he made a good part of his comic living portraying a “bad” violinist.
But my favorite has to be George Seagel, most recently of Just Shoot Me. His enjoyment of playing and singing far outweighs his inability to do either. I once saw him play his banjo and sing after two strings had broken. It didn’t deter him a bit.
Well, I can think of a host of great records made by sloppy, amateurish “garage bands” in the 1960s.
The Kingsmen certainly weren’t great musicians, but their “Louie Louie” was a great record.
Then there’s “Psychotic Reaction” by the Count 5. Again, a great record made by bad musicians.
Many other songs come to mind… “Pushing Too Hard” by the Seeds, “Gloria” by the Shadows of Knight.
That’s sometimes the charm of rock- you don’t always HAVE to be a virtuoso. You could put together an all-star band: Buddy Rich on drums, Jimi Hendrix on guitar, Jaco Pastorius on bass, Arthur Rubinstein on piano, and you know what? They couldn’t have done “Louie Louie” ANY better than the Kingsmen.
I understand that the members of U2 barely knew how to hold their instruments, let alone play them, when they started the band. I imagine that The Edge is much more capable these days, but if you listen to the early songs like I Will Follow you can tell he doesn’t know how to play well, but nevertheless he’s making a very powerful musical statement.
The Stooges, Jesus love 'em, had very, very little technical ability, but worked well with what they had.
None of The Beatles ever learned how to read music and none was “classically” trained, but Paul McCartney was probably the most accomplished musician of the 4.
But what did The Beatles do?
I give these two people a tie for bad musicianship,
Courtney Love and Kurt Cobain.
Also, The Go-Go’s couldn’t play when they were first formed, Belinda Carlisle had to recruit actual people who could play.
Yeah, because they’re dead now. 
And I do think “Louie Louie” needs some stacatto eighth note fretless runs.
The Velvet Underground. Sublime noise.
The champ here is surely “skronk” guitarist Arto Lindsay, who plays guitar without, in the conventional sense, knowing how to play it at all. In fact he doesn’t even bother tuning it. I’m not familiar with his work under his own leadership–often Brazilian-flavoured sessions, I gather–, but know his work on a number of John Zorn’s more abrasive discs–The Big Gundown, Filmworks, Locus Solus.
Eugene Chadbourne I think could, at one point in his career, actually play quite well. But the layers of schtick, anti-technique, deliberately annoying playing, &c have piled up so thickly in intervening decades that I’d doubt he can nowadays do anything except, er, what he does. At least, that’s my impression from the pretty unimpressive attempts at “straight” jazz standards on Strings, a fairly recent solo Chadbourne disc on Intakt.
Anyway, instrumental virtuosity has always been rather an ambiguous blessing in jazz: the canon is full of players like Bix Beiderbecke, Miles Davis & Don Cherry whose significance far outshines their technical limitations.
[Nitpick]
Actually, a fair bit of their catalogue was cover tunes of older stuff. Granted, the tunes in question were “speed-metal” versions of the originals, but it’s not like they were unrecognizable. Yeah, they only knew three chords, but they had them three down solid, man. 
[/Nitpick]
Wasn’t John Cale a classically trained violinist?
Bob Dylan can’t sing.
Didn’t stop him recording some of the best songs ever.
They were still accomplished musicians. Most contemporary popular musicians aren’t classically trained, so the Beatles don’t really qualify for the OP’s call for musicians who are “terrible in purely technical terms or unable to play in the proper sense, but manage to create inspiring or stunning music with their instruments.”
The Sex Pistols fit this category though. I saw a documentary where someone - possibly one of The Clash was saying that they saw the Sex Pistols play, and they were awful - “But I wanted to go out and be awful too!”
It’s what’s brilliant about rock.
Jimmy Buffett. He’s a mediocre guitar picker, although the Coral Reefer band has had some legendary talent in it at various times. But the man has created a Monster in his fan base and has some rather catchy tunes and hooks.