SQL Server Performance

SQL Server Guest Account - don't leave as it is!

Discussion in 'SQL Server Knowledge Sharing Network (SqlServer-qa' started by satya, Jul 31, 2007.

  1. satya Moderator

    Guest, as the name suggest do you need it on your SQL Server database. This is a special user 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.As it sounds the best practice is suggested that the guest user not be enabled under any circumstances. Although the guest user remains in all of the user and system databases performing the same general functionality in the SQL Server 2000 and previous versions, at the same time you can revoke the access as mentioned in BOL link here. In the previous versions including SQL 2005 it is always needed in the master and tempdb databases including when the Log Shipping is used with SQL Server Enterprise Edition. To get information about existence of this account on all the databases execute:
    EXEC sys.sp_MSforeachdb 'SELECT * FROM sysusers'
    GO
    Also the PROFILER (server side trace) behaves differently when guest account is enabled on the database that is used in this trace. By default if that user has permissions on the target database, impersonation has succeeded, and the trace is replayed with that login. But that user does not have permissions on the target database, the server checks for a Guest user on that database and depending on the results, one of the following occurs:

    1. If a Guest account exists, the trace is replayed as the Guest account.
      • If no Guest account exists on the target database, an error is returned and the trace is replayed as that login.
    • Overall the best practices for database object authorization is as follows:
      · Encapsulate access within modules.
      · Manage permissions via database roles or Windows groups.
      · Use permission granularity to implement the principle of least privilege.
      · Do not enable guest access.
      · Use users without logins instead of application roles

Share This Page