The SQL login user e.g., does belong to a role, but nothing but dbo has CONNECT right to the database.
Has tested a little further, and found that the user can use the stored procedures it has execute rights, but it can't connect to a database using Management Studio.
So if I try to connect through Management Studio, and browse my database I get an error.
But if I just open a New Query, I can do stuff like "USE myDatabase", "EXEC MyProc", "SELECT * FROM dbo.MyFunction", etc...
So I can perform all the things I have execute/select rights to, just not Connect and browse using the Management Studio.
This leaves me to think that I actually would prefer that my users don't have CONNECT rights on the live servers either.
But I would like to hear from someone who knows explicitly how this works, before I revoke the CONNECT option.