I wrote a question of the day recently that seemed to catch many people. The question had to do with mapping users when a login isn’t specified in the call. The behavior is to auto match existing logins with the same name. About 60% of the people answering the next day got it right, but a third missed it, expecting an error to be thrown.
One of the commenters was surprised that more people didn’t know this. I’d hope people knew this, though to be fair, I bet lots of people manage security through SSMS or a GUI and never write security code. I know I did for years early on. However I really think that the third of the people that got this wrong in its behavior, are actually right about how SQL Server security should work.
We do not want ambiguity when we configure security. We should be sure that rights granted (or removed) are exactly those that we expect. A strong security system should not tolerate any unexpected behaviors.
Security should require specificity.