My experience is that, in practice, players actually like secret checks more often than they don’t. The feel-bad of “player agency” loss (what agency is there in rolling a die? It’s literally an agency destroying mechanic) occurs at the conceptual level, long before ever experiencing it at the table. Telling a player that just hid that they don’t think the guards can see them really heightens the immersion, and players tend (most of them, most of the time, on average) to get into that.

You can’t have tension when the player knows they rolled a 19 on the die.