Guidelines on revoking Guest user access in a database.

Article ID: 2186935 - View products that this article applies to.
Expand all | Collapse all

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 

   

 

 

 

 

 

 

 

 

 

 

 

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2186935 - Last Review: April 3, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2186935

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com