Guidelines on revoking Guest user access in a database.


Summary


In SQL Server, a special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through the guest user, it is suggested that the guest user not be enabled.
Revoke the guest user permission to access the database if it is not required.

The guest user cannot be dropped, but guest user can be disabled by revoking it’s CONNECT permission by executing REVOKE CONNECT FROM GUEST within any database. The guest user can not be disabled in master or tempdb.

More Information


The following script lists all databases that have CONNECT permission granted to the Guest account.

 
SET NOCOUNT ON
GO
DECLARE @DBName VARCHAR(255)
DECLARE @SQL VARCHAR(MAX)
DECLARE @SQLEXEC VARCHAR(MAX)
CREATE TABLE #TableVar (DatabaseName varchar(256), permission_name varchar(32), State_desc varchar(10))
DECLARE curDatabases CURSOR STATIC FOR
SELECT [name]
FROM master.sys.databases
WHERE database_id > 4
ORDER BY [name]
OPEN curDatabases
FETCH NEXT FROM curDatabases INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'insert into #TableVar SELECT DB_NAME() as DatabaseName,permission_name,state_desc FROM sys.database_permissions where grantee_principal_id = user_id(''''guest'''') AND state = ''''G'''' AND type = ''''CO'''' '
SET @SQLEXEC = 'USE ' + QUOTENAME(@DBName) + ';EXEC (''' + @SQL +''')'
EXEC(@SQLEXEC)
FETCH NEXT FROM curDatabases INTO @DBName
END
CLOSE curDatabases
DEALLOCATE curDatabases
Select * from #TableVar
Drop table #TableVar
GO
 

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

 

Rule software

 Rule title

 Rule description

Product versions against which the rule is evaluated 

SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

Guest Permissions

The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect when any user created database has the guest user access enabled. If you run the BPA tool and encounter warning with the title of Engine - Guest Permissions, the guest user is enabled in at least one user created database.

SQL Server 2008
SQL Server 2008 R2

SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

Guest Permissions

 

 

The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect when any user created database has the guest user access enabled. If you run the BPA tool and encounter warning with the title of Engine - Guest Permissions, the guest user is enabled in at least one user created database.

SQL Server 2012