Sunday, January 16, 2011

‘Guest’ user account in SQL Server 2000/2005:

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.

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.

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.

Please refer 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

Remoce/Disable 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

GO

EXEC sp_revokedbaccess ‘guest’

GO USE

GO

REVOKE CONNECT FROM GUEST

GO

Source: SQL Server Books online
 

No comments:

Post a Comment