The Guest User permits access to a database for any logins that are not mapped to a specific database user.

 

Notes on Guest

Created for each database.
Disabled by default on user databases
Enabled by default on System databases, TempDB, Master, MSDB (Leave it that way, see Aaron Bertrand post below)
If you use Replication, The distribution database will have Guest account enabled (again treat this as a system database and don’t make changes!) Also note that all the scripts ive found don’t check if the database is distribution just on id > 4 so don’t just ran these scripts without double checking! or you will kill your replication.

Behaves differently in SQL 2000, compared to 2005 see the MSSQLTips for more details.

Microsoft Query to find enabled Guest Accounts:

SET NOCOUNT ON GO
DECLARE @DBName VARCHAR(255) DECLARE @SQL VARCHAR(MAX) DECLARE @SQLEXEC VARCHAR(MAX) CREATE TABLE #TableVar (DatabaseName varchar(256), permission_name varchar(32), State_desc varchar(10))
DECLARE curDatabases CURSOR STATIC FOR
SELECT [name] FROM master.sys.databases WHERE database_id > 4 ORDER BY [name]
OPEN curDatabases FETCH NEXT FROM curDatabases INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'insert into #TableVar SELECT DB_NAME() as DatabaseName,permission_name,state_desc FROM sys.database_permissions where grantee_principal_id = user_id(''''guest'''') AND state = ''''G'''' AND type = ''''CO'''' 'SET @SQLEXEC = 'USE ' + QUOTENAME(@DBName) + ';EXEC (''' + @SQL +''')'EXEC(@SQLEXEC)
FETCH NEXT FROM curDatabases INTO @DBName
END CLOSE curDatabases DEALLOCATE curDatabases Select * from #TableVarDrop table #TableVarGO

http://support.microsoft.com/kb/2186935

Links

You should not disable the guest user in the msdb database in SQL Server

http://support.microsoft.com/kb/2539091

If you disable the guest user, please read this!

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/29/if-you-disable-the-guest-user-please-read-this.aspx

SQL Server – database guest user account

http://www.mssqltips.com/sqlservertip/1172/sql-server-database-guest-user-account/

SQL SERVER – Detecting guest User Permissions – guest User Access Status

http://blog.sqlauthority.com/2012/02/23/sql-server-detecting-guest-user-permissions-guest-user-access-status/

SQL SERVER – Disable Guest Account – Serious Security Issue
http://blog.sqlauthority.com/2012/02/20/sql-server-disable-guest-account-serious-security-issue/

SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database
http://blog.sqlauthority.com/2012/02/24/sql-server-guest-user-and-msdb-database-enable-guest-user-on-msdb-database/

Guidelines on revoking Guest user access in a database
http://support.microsoft.com/kb/2186935

Security Checklists for the Database Engine
http://msdn.microsoft.com/en-us/library/ff848778.aspx

Advertisements