SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database

I have written a few articles recently on the subject of guest account. Here’s a quick list of these articles:

One of the advices which I gave in all the three blog posts was: Disable the guest user in the user-created database. Additionally, I have mentioned that one should let the user account become enabled in MSDB database. I got many questions asking if there is any specific reason why this should be kept enabled, questions like, “What is the reason that MSDB database needs guest user?” Honestly, I did not know that the concept of the guest user will create so much interest in the readers. So now let’s turn this blog post into questions and answers format.

Q: What will happen if the guest user is disabled in MSDB database?
A:  Lots of bad things will happen. Error 916 – Logins can connect to this instance of SQL Server but they do not have specific permissions in a database to receive the permissions of the guest user.

Q: How can I determine if the guest user is enabled or disabled for any specific database?
A: There are many ways to do this. Make sure that you run each of these methods with the context of the database. For an example for msdb database, you can run the following code:

USE msdb;
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'

There are many other methods to detect the guest user status. Read them here: Detecting guest User Permissions – guest User Access Status

Q: What is the default status of the guest user account in database?
A: Enabled in master, TempDb, and MSDB. Disabled in model database.

Q: Why is the default status of the guest user disabled in model database?
A: It is not recommended to enable the guest in user database as it can introduce serious security threat. It can seriously damage the database if configured incorrectly. Read more here: Disable Guest Account – Serious Security Issue

Q: How to disable guest user?
A: REVOKE CONNECT FROM guest

Q: How to enable guest user?
A: GRANT CONNECT TO guest

Did I miss any critical question in the list? Please leave your question as a comment and I will add it to this list.

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

About these ads

5 thoughts on “SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database

  1. Hello Sir,
    The below query is showing status as GRANT always in my system, no matter guest user is disabled or enabled.

    USE msdb;
    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’

    Like

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

  3. What I have to do if I want to enable guest?
    The guest will have permission to edit records in sharepoint?
    What to do to enable anonimous to edit records in sharepoint?

    Like

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

  5. Under section:

    Q: How can I determine if the guest user is enabled or disabled for any specific database?

    This is the query to use to determine. The one shown joins to sys.server_permissions which is not relevant to a particular database:

    SELECT dp.name,
    perm.permission_name,
    perm.state_desc
    FROM sys.database_principals dp
    INNER JOIN sys.database_permissions perm ON dp.principal_id = perm.grantee_principal_id
    WHERE dp.name = ‘guest’
    AND perm.permission_name = ‘CONNECT';

    Like

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