I have written a few articles recently on the subject of guest account and MSDB Database. 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 the 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 the 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 in the context of the database. For an example of 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 the database?
A: Enabled in master, TempDb, and MSDB. Disabled in the 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 the 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 (https://blog.sqlauthority.com)
3 Comments. Leave new
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’
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?
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’;