A few days ago, I helped one of my customers with a cyber security review questionnaire. One of the sections asked about the guest users and permissions. The customer asks me, what danger is in the guest - it is a default part of SQL Server installation. So today I want to discuss a little about "guest".
What is the "guest"?
The Oxford dictionary: a person who is invited to visit the home of or take part in a function organized by another.
The Cambridge dictionary: a person who is staying with you, or a person you have invited to a social occasion, such as a party or a meal.
Simple, the person who you welcome into your house, your office, etc.
Now, let's see what is the guest in the database context. The guest user allows database access to logins who do not have associated users inside SQL Server databases. In another word, anyone who has access to the SQL Server can connect to each database in our system.
Well, in today's cyber security reality, "anyone" it's not really a "welcome person".
What does that mean and what to do?
Guest user permits access to a database for any logins that are not mapped to a specific database user. When SQL Server is installed by default, the guest user is disabled for security reasons. If the guest user is not properly configured, it can create a major security issue. If enabled, any user who had permission to the server is able to get into the database.
It is recommended to disable a guest user in every database as a best practice for securing the SQL Server. Important, this recommendation does not apply to master, tempdb, and msdb databases. It may cause some issues and the whole system failure.
By disabling guest user access from the user DBs will ensure, that members of PUBLIC server role may not be able to access user databases on SQL Server instance unless they have access to the database explicitly.
Check the configuration
The guest user cannot be dropped, but it can be disabled by revoking the CONNECT permission. My script from Madeira Toolbox on Github can check each database in the instance and prepare REVOKE command for the necessary DBs.
Important!
This problem does not exist on the Azure SQL database, because the user should be always created on master. Guest access to master database is not enabled by default on SQL Azure Database service.
In order to have this kind of user, check Azure AD Guest Users for Azure SQL and Azure AD guest .