guest account RRS feed

  • Question

  • Could anyone explain me please how can I check if the guest account is disabled ? 

    I read that the guest account when enabled in the database permits SQL Server logins that are not mapped to a database user to enter the database as the guest user. 

    I see the guest account with no login asociated in my database, is it enough to reduce the risk? 


    Monday, July 17, 2017 12:36 PM

All replies

  • To be sure, DENY Connect to Guest:

    use mydatabase

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Monday, July 17, 2017 12:48 PM
  • The Northwinds and pubs sample databases had the guest account enabled when they shipped with the data engine. The latest versions of these sample databases provided by Microsoft for download have this account disabled.

    The guest account has not been enabled in Microsoft samples since this and are not enabled by default in SQL Server.

    Monday, July 17, 2017 1:37 PM
  • Here is a query that will list all the users that can connect:

    SELECT Princ.name, perm.permission_name, perm.state_desc
    FROM sys.database_permissions AS perm
    JOIN sys.database_principals AS princ
      ON perm.grantee_principal_id = princ.principal_id
    WHERE perm.permission_name = 'CONNECT';

    This will include the dbo user, which can represent multiple logins, such as all members of the sysadmin fixed server role. So don't make the mistake of thinking that this results of this query tell you everything you might need to know. 

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Teige Gao Tuesday, July 18, 2017 2:59 AM
    Monday, July 17, 2017 3:28 PM
  • I see the guest account with no login asociated in my database, is it enough to reduce the risk? 

    Although the guest account exists in all databases, the guest database user has no connect permissions by default (except in system databases). No CONNECT permission effectively disables the account un user databases. Also, note that the guest account is a member of only the public role so has very limited permissions by default.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, July 17, 2017 3:40 PM