SQL SERVER – Detecting guest User Permissions – guest User Access Status

Earlier I wrote the blog post SQL SERVER – Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.

Here are the comments of Manoj:

1) How do we know if the uest user is enabled or disabled?
2) What is the default for guest user in SQL Server?

Default settings for guest user

When SQL Server is installed by default, the guest user is disabled for security reasons. If the guest user is not properly configured, it can create a major security issue. You can read more about this here.

Identify guest user status

There are multiple ways to identify guest user status:

Using SQL Server Management Studio (SSMS)

You can expand the database node >> Security >> Users. If you see the RED arrow pointing downward, it means that the guest user is disabled.

SQL SERVER - Detecting guest User Permissions - guest User Access Status guestdisabled

Using sys.sysusers

Here is a simple script. If you notice column dbaccess as 1, it means that the guest user is enabled and has access to the database.

SELECT name, hasdbaccess
FROM sys.sysusers
WHERE name = 'guest'

Using sys.database_principals and sys.server_permissions

This script is valid in SQL Server 2005 and a later version. This is my default method recently.

SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

Using sp_helprotect

Just run the following stored procedure which will give you all the permissions associated with the user.

sp_helprotect @username = 'guest'

Disable Guest Account

REVOKE CONNECT FROM guest

Additionally, the guest account cannot be disabled in master and tempdb; it is always enabled. There is a special need for this. Let me ask a question back at you:

In which scenario do you think this will be useful to keep the guest, and what will the additional configuration go along with the scenario?

Note: Special mention to Imran Mohammed for being always there when users need help.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video
Next Post
SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database

Related Posts

15 Comments. Leave new

  • in case u forget the administrator,sa or any sysadmin login’s password , Guest account can be usable

    Reply
  • Dear Dave,

    If there is such a big security issues with Guest account, why MS is not removing it grom the future versions of SQL server?In case DBA forget sa or administrator account password & we use guest account for the same, but here we are bypassing the basic responsibilities of DBA for remebering the cruicial passwords.

    Please share your thoughts

    Regards,
    Sanjeev Kumar

    Reply
  • Guest Account is used in installation of the sql server and also in the log shipping.

    Reply
  • Dear Dave, your default method (Using sys.database_principals and sys.server_permissions) is giving me GRANT in state_desc column, even the guest is disabled.

    Reply
  • Guest Account in the MASTER database is like a default profile. If I create a new login which has not been granted access to any database and when this login is used to connect to the SQL Server, it will by default point to master database (can access the TEMPDB and MSDB databases also) with the rights conferred to the GUEST account for eg. query system tables. The guest login is a member of the public server role and inherits the permissions of this role.

    Guest Account in the TEMP database – besides other things, this is needed for default logshipping functionality. I know this was applicable for SQL 2000 but not sure how is it with SQL 2005/2008 since I have not worked with logshipping after SQL 2000.

    Reply
  • On my server, the red arrow is pointing down in SSMS. Using code above to select sys.sysusers displays 1. How can the two be different? Don’t they mean the same thing?

    Reply
  • can we intialize bool datatype to null in sql server? if yes, then how?

    Reply
  • Hi I have tried asking friends of mine how to solve this..

    column1 column2
    1 wo 99808944|F|Q:\Links\WO 99808944.PDF 99825525|F|Q:\LINKS\99825525.PDF
    2 |F|HRMC\UNABLE TO LOCATE REPORT FOR JANUARY 2010.TXT not located memo|F|HRMC\NOTLOCLTHRMC JAN 10 (2).DOC not located list|F|HRMC\COPY OF HRMC 1-10 NOT LOCATED.XLS

    Indeed to get the files from these sample columns
    Column1 Column2
    1 WO 99808944.PDF
    1 99825525.PDF
    2 UNABLE TO LOCATE REPORT FOR JANUARY 2010.TXT
    2 NOTLOCLTHRMC JAN 10 (2).DOC
    2 COPY OF HRMC 1-10 NOT LOCATED.XLS

    Reply
  • i think your system catalog query with sys.server_permissions should be
    sys.database_permissions

    Reply
  • I just want to know what exactly a user can do with public access on a database

    Reply
  • Hi Dave

    I need to Find All Logins With VIEW SERVER STATE Permissions.

    Thanks
    Rakesh

    Reply
  • I am thinking of making a separate database to hold utility functionality for all application databases. These utilities should be universally available (or, they don’t belong in this new database). I am going to use the guest account to provide access to all Public Methods (aka procs/funcs) in this database, so I don’t have to add all users individually.

    Reply
  • Please update your query

    SELECT name, permission_name, state_desc
    FROM sys.database_principals dp
    INNER JOIN sys.server_permissions sp
    ON dp.principal_id = sp.grantee_principal_id
    WHERE name = ‘guest’ AND permission_name = ‘CONNECT’

    should be sys.database_permissions not server_permissions

    Reply
    • Agreed…when run as is, it will always return entries (usually 4) based on the server’s end points rather than DB permissions.

      Reply

Leave a Reply

Menu