SQL Server Guest User – Still a Serious Security Threat

One of the security recommendation inside SQL Server Security Best Practice white paper for guest user is that, to disable guest access in every database (expect “master”, “msdb” and “tempdb”) on SQL Server instance, and it should not be used in any circumstances. By default, guest user exists in all user and system databases. Having guest user enabled inside databases, leaves a security risks of unauthorized (or unaudited) access to the data because Guest user allows database access to logins who do not have associated users inside SQL Server databases. By disabling guest user access from the user databases will ensure, that member of PUBLIC server role may not be able to access user databases on SQL Server instance, unless they have access to the database explicitly.

I have written following script which you can use to list all databases with guest user access enabled:

USE [master]


DECLARE @First [smallint]
	,@Last [smallint]
	,@DBName [varchar] (200)
	,@SQLCommand [varchar] (500)
	,@DBWithGuestAccess [nvarchar] (4000)

IF OBJECT_ID('tempdb..#GuestUsersReport') IS NOT NULL
	DROP TABLE #GuestUsersReport

CREATE TABLE #GuestUsersReport (
	[Database] [varchar](256)
	,[UserName] [varchar](256)
	,[HasDbAccess] [varchar](10)

DECLARE @DatabaseList TABLE (
	[RowNo] [smallint] identity(1, 1)
	,[DBName] [varchar](200)

INSERT INTO @DatabaseList
SELECT [name]
FROM [master]..[sysdatabases] WITH (NOLOCK)
WHERE [name] NOT IN ('master', 'tempdb', 'msdb')
ORDER BY [name]

SELECT @First = MIN([RowNo])
FROM @DatabaseList

SELECT @Last = MAX([RowNo])
FROM @DatabaseList

WHILE @First <= @Last
	SELECT @DBName = [DBName]
	FROM @DatabaseList
	WHERE [RowNo] = @First

	SET @SQLCommand = 'INSERT INTO #GuestUsersReport ([Database], [UserName], [HasDbAccess])' + CHAR(13) + 'SELECT ' + CHAR(39) + @DBName + CHAR(39) + ' ,[name], CASE [hasdbaccess] WHEN 0 THEN ''N'' WHEN 1 THEN ''Y'' END ' + CHAR(13) + 'FROM [' + @DBName + ']..[sysusers] WHERE [name] LIKE ''guest'' AND [hasdbaccess] = 1'

	EXEC (@SQLCommand)

	SET @First = @First + 1

FROM #GuestUsersReport WITH (NOLOCK)


IF OBJECT_ID('tempdb..#GuestUsersReport') IS NOT NULL
	DROP TABLE #GuestUsersReport

Well after running the above script, if you find any databases with guest access enabled, then use the REVOKE CONNECT statement to revoke the access of guess access from user database:

--Specify database name in USE statement
USE [<SpecifyDatabaseName>]


This recommendation does not apply to “master”, “msdb” and “tempdb” database. For more information, see “KB #2539091 : You should not disable the guest user in the msdb database in SQL Server” and “KB #2186935 : Guidelines on revoking Guest user access in a database“.


4 thoughts on “SQL Server Guest User – Still a Serious Security Threat

  1. I had an error — you cant revoke guest on master or tempdb either.

    But I came up with this code snippet to do it automatically
    exec sp_msforeachdb ‘if ”?” ”msdb” and ”?” ”master” and ”?” ”tempdb” use [?]; begin; select ”?” as db_name; REVOKE CONNECT TO [guest]; end ;’

    Since it doesn’t matter if you revoke the privilege multiple times.


  2. And if you disable guest access to master, you will lose execute rights on some essential extended stored procedures which amongst other things can cause some of the OLEDB providers in ADO.Net to be unable to connect to your database.


  3. Thanks for this script!

    I got this message while running it:

    Msg 942, Level 14, State 4, Line 3
    Database ‘XXX’ cannot be opened because it is offline.

    and had to rewrite the @DatabaseList query as follows:

    INSERT INTO @DatabaseList
    SELECT d1.[name]
    FROM [master]..[sysdatabases] d1 WITH (NOLOCK)
    JOIN [sys].[databases] d2 on d1.dbid = d2.database_id
    WHERE d1.[name] NOT IN (‘master’, ‘tempdb’, ‘msdb’)
    AND d2.state_desc = ‘ONLINE’ — see https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017
    ORDER BY d1.[name]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s