SQL Server Database Guest User Account

By:   |   Comments (2)   |   Related: > Security


Who invited all of these guests to my database?  Did you know that the guest user account even exists in your SQL Server databases?  Do you know that SQL Server needs the guest user account for particular functionality that differs in SQL Server 2000 vs. 2005?  Do you know if your SQL Server 2000 and 2005 databases have the guest login in their respective databases?  Do you consider this a problem or the reality for your SQL Server environment?


The guest user account is in both SQL Server 2000 and 2005, but I vaguely remember that it has been around since the SQL Server 6.5 days and probably earlier.  This user account is used by SQL Server to access objects across databases if explicit rights are not granted.  This account is relied on for some SQL Server 2000 system functions (master and tempdb) and needs to be maintained for SQL Server to function properly.  Unfortunately, in some respects the guest user account can be a considered a threat from an application perspective that can be resolved in user defined databases.

Does the guest user account pose a threat to my SQL Servers?

This answer differs from SQL Server 2000 to 2005 and based on the needed security of your data.  Here are some key points to consider:

  • SQL Server 2000 - The guest user account existed in all of the databases including new user defined databases because the user existed in the Model database.  This user account is mandatory for the master and tempdb databases for SQL Server to operate properly and should never be removed from these databases.  The need in the remainder of the user defined databases is application dependent and MUST be tested thoroughly.
    • For additional information reference - guest User
  • SQL Server 2005 - With the introduction of the schema, database users are separated from objects providing an additional layer of security over SQL Server 2000.  Although the guest user remains in all of the user and system databases performing the same general functionality in the SQL Server 2000 world it, the guest user can have its CONNECT permissions revoked.
  • Data security - If you want to ensure data is not incorrectly accessed, then review the code below to first find out if the guest user account exists and is enabled, then it is necessary to determine if your application accesses objects without explicit rights relying on the guest user account.

Is the guest user account ever needed?

As previously mentioned, it is always needed in the master and tempdb databases for both SQL Server 2000 and 2005.  Another scenario where it is needed is related to the default log shipping functionality that ships with SQL Server 2000 Enterprise Edition.  For more information reference - Frequently asked questions - SQL Server 2000 - Log shipping

*** NOTE *** - Additional needs may exist for the guest user account based on default functionality in SQL Server and based on how third party or custom applications were developed.  As such, it is imperative to thoroughly test your applications prior to modifying the guest user account rights.

How can I find out if the guest user account exists?

Please reference the scripts below to determine if the guest user account exists in each of your databases:

SQL Server 2000 and SQL Server 2005

EXEC sp_MSforeachdb 'USE [?];
SELECT * FROM sysusers;'

What login does the guest user account map to?

This is the key item, the guest user account does not need map to a login, this user account would be used in a database where explicit rights are not granted to the user mapped to the authenticated user.

How can I avoid relying on this user account?

There are some system portions of SQL Server 2000 that require the guest user account.  So this functionality cannot be changed without braking SQL Server, but for business applications explicit rights should be granted.

What sorts of tests should I perform?

  • Pay special attention to all applications that access multiple databases
  • Test your overall applications based on a comprehensive application test plan
  • Perform routine administrative functions
  • Execute all batch, nightly, weekly, monthly, etc. processes
  • Run all third party applications
  • Be sure to have your development and test environments setup with the same guest user account configurations as the production environment

How can I remove/disable the guest user account?

Once you have thoroughly tested your applications, the following scripts can be used to remove/disable the guest user account:

SQL Server 2000 SQL Server 2005
USE <Database Name>
EXEC sp_revokedbaccess 'guest'
USE <Database Name>
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, April 11, 2008 - 9:51:58 AM - admin Back To Top (862)


Yes - You are correct.  We also have this tip related to sp_MSForEachDB:


Thank you,
The MSSQLTips.com Team

Friday, April 11, 2008 - 4:44:18 AM - tosc Back To Top (861)


to explicit determine the guest user account I use your query as follow:

EXEC sp_MSforeachdb 'USE [?];
IF DB_ID(''?'') > 4
SELECT * FROM sys.sysusers WHERE name=''guest'';'

get free sql tips
agree to terms