SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database
- by pinaldave
I have written a few articles recently on the subject of guest account. Here’s a quick list of these articles:
SQL SERVER – Disable Guest Account – Serious Security Issue
SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.
SQL SERVER – Detecting guest User Permissions – guest User Access Status
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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology
