Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login

Question

When a new login is created in SQL Server it automatically defaults database to master.

enter image description here

We do not give permissions in master (systemdb) but in our user DB (CustomersDB) with db_datareader and db_datawriter.

We save the account.

Then a user is able to connect, why? Does this have to do with public permissions in master?

Answer 1

All logins exist in the master database. Why it is default is probably related to this, but you can change the default database to your user database if you wanted to. The default being master simply means they have to explicitly set the user database in the connection string for the application.

The default database has nothing to do on why the logon can connect to the instance. This comes from being granted connection privileges to the instance. This is done by default when using SSMS to create the login.

Additional Info

Public role is granted connect permissions and any login created is automatically part of that role.

The only permissions granted for every endpoint is Connect. The only thing they get at the server level is view any database.

enter image description here enter image description here

You actually cannot remove a login from the public role either, SQL Server does not let you because it is hard coded in there somewhere. You will most security standards for things like HIPPA or DoD (STIGs) want you to simply harden what the public role can do at the server level and within each database. See "INF: SQL Security - Restricting access to public..." for a bit more information on it all.

Creating a login straight from GUI or from just CREATE LOGIN and you will automatically see these permissions at the server level:

enter image description here

• Normoe: Thanks Shawn. As a follow-up question. I created an account with "no rights" I tested and it allowed me to login via SSMS as you stated. Are the connect rights embedded in the "public" role in master DB?
• Pieter Geerkens: @Normoe: Follow up questions go in a new question - not in a comment to an existing question. Comments can, and frequently will, be deleted without warning for a variety of reasons.
• user507: @PieterGeerkens since his "follow up question" related to the original question (mentions "public permissions") it is still relevant to the original question. There is also no reason these comments will be deleted since it would take a moderator's request to do so (at least on DBA.SE).