SQL SERVER – Detecting guest User Permissions – guest User Access Status
- by pinaldave
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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology
