Who is the SQL Server Guest User ?
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
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
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
No 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 GO
This 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.