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 (https://blog.sqlauthority.com)
15 Comments. Leave new
in case u forget the administrator,sa or any sysadmin login’s password , Guest account can be usable
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
Guest Account is used in installation of the sql server and also in the log shipping.
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.
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.
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?
can we intialize bool datatype to null in sql server? if yes, then how?
Make default value of that column to NULL
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
i think your system catalog query with sys.server_permissions should be
sys.database_permissions
I just want to know what exactly a user can do with public access on a database
Hi Dave
I need to Find All Logins With VIEW SERVER STATE Permissions.
Thanks
Rakesh
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.
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
Agreed…when run as is, it will always return entries (usually 4) based on the server’s end points rather than DB permissions.