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 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]
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

Best Practices

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.

http://support.microsoft.com/kb/2539091

http://msdn.microsoft.com/en-us/library/bb402861(v=SQL.100).aspx

]]>