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.

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 (http://blog.sqlauthority.com)

15 thoughts on “SQL SERVER – Detecting guest User Permissions – guest User Access Status

  1. 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

    Like

  2. 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.

    Like

  3. 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.

    Like

  4. Pingback: SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Identifying guest User using Policy Based Management « SQL Server Journey with SQL Authority

  6. 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?

    Like

  7. 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

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s