I really struggle to think of a time in TNG when we see the door-chime entry method not happen. And the only times we see someone enter a room without permission they first had to call security for permission and, I assume, this isn't something granted to just anyone who calls for it.
At the very most there may have been a small handful of times the door chime wasn't used which, as Christopher said, is just a TV trope we have to accept for the sake of faster-paced drama.
But pretty much always someone pressed the door chime button, the person inside the room says "Come!" then the doors open. What always sort of "bugged" me is that the large Starfleet emblem button on the exterior panel didn't seem to be the door chime button as more often people seemed to press one of the smaller buttons on the panel.
Now, what *is* odd is the lack of privacy when it comes to the holodeck. There are a few times in the series, at the very least, I can think of where people walk in on someone else's holodeck experiences. One is in "Hollow Pursuits" where Riker, Troi and Geordi go looking for Barcaly on the holodeck and walk in on him while he's sleeping in the arms of a holodeck version of Doctor Crusher. (And this was a fantasy world where Barcaly had created copies of all of the main cast. Including embarrassing versions of his male superiors and sensualized versions of his female superiors.)
Picard walks in on Scotty's recreation of the TOS Ent-Nil in "Relics."
Both Riker and Wesley walk in on Data's forest fantasy in the pilot.
And Captain Cockblock walks in on Riker and "Minuet" in "10110001." Seriously, had Picard been a few minutes later chances are he would've walked in on something *very* awkward.