What does the User Mapping tab of Login Properties really tell us.6
June 24, 2019 by Kenneth Fisher
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.
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.
Category: Microsoft SQL Server, Security, SQLServerPedia Syndication, SSMS | Tags: Microsoft SQL Server, security, SSMS
6 thoughts on “What does the User Mapping tab of Login Properties really tell us.”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Nice! I had a question though. How did you name a database using animal faces? Or was that just added for effect? It’s unique for sure.
It’s actually both completely normal and added for effect. I’ve decided (with absolutely no evidence) that having emoji’s for the name of a demo database makes the information more memorable.
In general though SQL handles emoji’s just fine 🙂
That is awesome! Thank you for sharing Sir
very interesting info on security and SSMS tab. TX for sharing!!
[…] What does the User Mapping tab of Login Properties really … […]
[…] What does the User Mapping tab of Login … – SQL Studies […]