By: Jeremy Kadlec | Comments (2) | Related: > 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.
- For additional information reference - Database Users
- 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
- If you are not familiar with the guest user account, then check out your SQL Servers and dig a little deeper to see if this login is being used
- If the guest user account is in use and it is an issue from a data vulnerability perspective, then follow this process:
- Research the explicit rights needed to prevent relying on the guest user account
- Setup the needed rights in a test environment
- Test your applications
- Build roll back scripts for your SQL Server and application related code
- Test the roll back scripts
- Deploy to production once properly tested in a test environment
- Perform post production deployment testing for the application to determine if the changes were successful or if the code needs to be rolled back
- For additional security tips on MSSQLTips.com, check out the following:
About the author
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
