I think vignette and film grain can both look great in small doses and in the right situations, but they have to be really subtle. If you can actively see them, it's already far too much. Unfortunately, most games go wayyyy overboard with them.
Film grain has a few psychological effects:
- it can evoke a sense of nostalgia, as if you're watching old footage
- it can add texture, which can stop players from perceiving flat textures as being as, well, flat, stop jagged edges from standing out as much, and adding a sense of depth
- it can make something feel more "genuine" - rather than being clean, edited, almost clinical feeling, film grain can make things feel a little more raw and believable
- it can add a "documentary feel", especially combined with camera shake (another thing a lot of games are far too liberal with, but that can be great in the right moment/dosage) which can make the player/viewer feel more like they're in the action themselves
- a lot of cinematic masterpieces were filmed on analogue film. Film grain is something people subconsciously associate with something being cinematic, to the point where a lot of modern film/TV that's shot on digital cameras (and therefore doesn't have film grain) will have digital noise added to emulate the effect. Games do it for the same reason.
As for vignette, I think this is far more niche and definitely something that isn't used well in a lot of games. The biggest reasons to use it are:
- to draw the player/viewer's eye to the centre of the screen/frame
- to reduce the player's peripheral vision - this can be good for horror games to add a feeling of claustrophobia, or in any genre when the player is wounded (although many games will add fairly distinctive bloody overlays rather than a simple vignette)
- during low-light situations - like above, but it can really sell the darkness a lot more (on top of actual low-lighting effects) if there's a well-done vignette.
The thing is, both things need to be used in tasteful quantities, and ideally done dynamically. Just plastering them over the entire screen all the time looks terrible, whereas using them in specific situations and being more selective with how they're applied - like in low-light situations, certain environments, or specific story moments - can work really well. And most developers simply don't do that.
Personally, I'd say a game feels like "hand holding" when there's no room for player agency. The key thing that sets video games aside as a medium is interactivity (as a two-way relationship) and player expression, and I think when a game is referred to as "hand holding", it's because there's little-to-no room for players to express themselves - no obstacles to find creative solutions to, no way to play the game/section in different styles, etc.
I think the only real examples of "good" hand holding that spring to mind are optional tutorial sections where the player has specifically requested to be introduced to certain mechanics.
And there are absolutely games that aren't handholdy enough, too - where players are just thrown in at the deep end without any explanation for what's going on or how to play. Sometimes the lack of direction can be a good thing - exploration (of both a game's world and its systems) can be exciting and rewarding, like discovering for yourself how to survive and advance in a survival game - but some games are so complex and overwhelming that players not being introduced to things more gradually can be really off-putting.
It's difficult to say where I'd draw the line, though; I think it's a somewhat personal thing and something that varies from game to game. Quite a few of the games I enjoy, such as Path Of Exile, are far too overwhelming for the "average person". And a lot of "cinematic third-person action adventure" games (like the majority of Sony's first-party titles) feel overly-restrictive to me, like I'm just being railroaded without really having to engage my brain much and without really being able to express myself through the gameplay. But the window of "handholdyness" that's appropriate for me is wildly different to the average person, who does enjoy Sony games and probably doesn't enjoy Path Of Exile.
I think this also somewhat ties into the idea of accessibility versus depth. The more handholdy a game is, the more accessible it is. But making a game accessible quite often involves removing the intricacies and complexities that result in mechanical/systematic depth. Is it possible for a game to have incredible depth, complexity and variety while also being intuitive and accessible for a brand nee player?