In case it’s not clear, we are talking about the Login Properties GUI in SQL Server Management Studio (SSMS). Now, if you’ve ever been to my security session (next opportunity) then you realize that I’m really impressed with SSMS. How security is handled? Not so much. Mostly because there are a lot of different places where security is listed, and sometimes you have to go to multiple places in order to get a feel for the actual permissions. Well, now I have a new fun fact about the security GUI.
The User Mapping tab of Login Properties is a pretty useful screen. It shows a list of all of the databases you can see and what roles you are a member of in each.
But what does that really mean? I’d always assumed that the checkbox in the top area meant you have a user in the database. No, not so much.
USE [StackOverflow2013];
REVOKE CONNECT FROM [Cowardly
];
So if there is no checkbox next to a database that ONLY tells you that you don’t have the CONNECT permission. Note: It will only tell you what the associated USER name and Default Schema are if you have that permission, regardless of the fact that associated user exists. If you look in the bottom security though you are still a member of different roles!!!!
Does that really matter? I mean if you don’t have the connect permission then you don’t have access right? No, not so much. Active Directory/Windows groups and even roles can have the CONNECT permission. So in this particular example if Cowardly
doesn’t have the CONNECT permission but is a member of the ICanConnect role which just has the CONNECT permission then they will be able to connect to the database and then make use of any permissions they have directly through their user or any roles/groups they may belong to.