SQL Server Database Guest User Account


By:   |   Updated: 2007-02-05   |   Comments (2)   |   Related: More > Security

Problem
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?

Solution
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;'
GO

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>
GO
EXEC sp_revokedbaccess 'guest'
GO
USE <Database Name>
GO
REVOKE CONNECT FROM GUEST
GO

Next Steps



Last Updated: 2007-02-05


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Tosc,

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

http://www.mssqltips.com/tip.asp?tip=1414

Thank you,
The MSSQLTips.com Team


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

Hi,

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

EXEC sp_MSforeachdb 'USE [?];
--IF THE DATABASE IS NOT A SYSTEM DATABASE
IF DB_ID(''?'') > 4
SELECT * FROM sys.sysusers WHERE name=''guest'';'
GO


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools