Have you noticed the ‘guest’ user in all of your databases?
Who The Guest User?
When you login to the SQL Server, it first verifies you for authentication on the server. If it succeeds, SQL Server checks whether your login is associated or mapped with a database user in the database that the login is attempting to access. If so, SQL Server grants the login access to the database as the database user.
If no such mapping exists, SQL Server checks whether a guest username exists. If so, the logged in user is granted access to the database as guest. If the guest account does not exists, SQL Server denies access to the database.
Let’s run through this in a demo.
First we will create a database.
USE master GO IF DATABASEPROPERTYEX('DB_GUEST','Version') > 0 DROP DATABASE DB_GUEST CREATE DATABASE DB_GUEST GO Let me create a use and allocate permission for some other database. USE [master] GO CREATE LOGIN [test_user] WITH PASSWORD=N'Pa$$w0rd' GO USE [AdventureWorks] GO CREATE USER [test_user] FOR LOGIN [test_user] GO
Now let’s, create a user and allocate permissions for another database.
USE [master] GO CREATE LOGIN [test_user] WITH PASSWORD=N'Pa$$w0rd' GO USE [AdventureWorks] GO CREATE USER [test_user] FOR LOGIN [test_user] GONo we will attempt to use this account to access adventure works and DB_Guest database.
Use AdventureWorks GO
There is no issue here since test_user has public permissions for the AdventureWorks database. Now let’s us try with the DB_GUEST database.
Use DB_GUEST GOThis yields the below result:
Msg 916, Level 14, State 1, Line 1 The server principal "test_user" is not able to access the database "DB_GUEST" under the current security context.
By default, the guest account is disabled since it is disabled in the model database. If you want to enable the guest account by default, enable this in the model database. This account is enabled in other system databases such as, Master, Msdb, Temdb and Distributor but not for Model.
There is no way of enabling or disabling the guest account from the user interface and you cannot drop or create it either.
This is to enable it using TSQL.
USE DB_GUEST GO GRANT CONNECT TO Guest
In case you need to revoke the permission this can be done using the below TSQL.
REVOKE CONNECT FROM Guest
Note that you are not allowed REVOKE guest permissions for the master, msdb, tempdb and distributor. However, you can revoke or grant guest user access in the Model database.
Now, let us try to connect again with user test_user and you should have no issues of getting into the database.
If you provide any other permission to guest user, test_user will acquire the permissions of the guest user.
How it can be helpful ?
Let us assume that, you have database access and you need to provide everyone with read access. Rather than giving the permission to each and every user, easiest way is to enable the guest user and provide the guest user with the db_datareader database role.
USE [DB_GUEST] GO EXEC sp_addrolemember N'db_datareader', N'guest' GO
For secuirty reasons you should revoke the guest user any permission to access the database if it is not required.
The guest user account may be dropped, however, the guest user can be disabled by revoking the CONNECT permission by executing REVOKE CONNECT FROM GUEST from within any database except the master or tempdb databases.
For more information.