What does the User Mapping tab of Login Properties really tell us.

4

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.

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.

4 thoughts on “What does the User Mapping tab of Login Properties really tell us.

  1. Michael Tormos says:

    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.

  2. very interesting info on security and SSMS tab. TX for sharing!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,084 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: