As best practice it is recommended to disable guest user in every user database, i.e. not master, msdb and tempdb, to improve the security of SQL Server. Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission.
Use the script below to identify which databases have guest user enabled.
USE master; GO DECLARE @database_name SYSNAME ,@sqlcmd NVARCHAR(4000) DECLARE databases_cursor CURSOR FOR SELECT NAME FROM sys.databases WHERE STATE IN (0) AND database_id > 4 ORDER BY NAME CREATE TABLE #guest_users_enabled ( database_name SYSNAME ,user_name SYSNAME ,permission_name NVARCHAR(128) ,state_desc NVARCHAR(6) ) OPEN databases_cursor; FETCH NEXT FROM databases_cursor INTO @database_name; WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd = N'use ' + @database_name + '; insert into #guest_users_enabled SELECT ''' + @database_name + ''' as database_name, name, permission_name, state_desc FROM sys.database_principals dpr INNER JOIN sys.database_permissions dpe ON dpr.principal_id = dpe.grantee_principal_id WHERE name = ''guest'' AND permission_name = ''CONNECT''' EXEC sp_executesql @sqlcmd FETCH NEXT FROM databases_cursor INTO @database_name; END SELECT database_name ,user_name ,permission_name ,state_desc FROM #guest_users_enabled ORDER BY database_name ASC DROP TABLE #guest_users_enabled CLOSE databases_cursor; DEALLOCATE databases_cursor; GO
Use the script below, changing the placeholder database with the database identified in the script above, to revoke permission for the guest user to connect to that database.
USE [database name]; GO REVOKE CONNECT FROM GUEST; GO
Tidbytez 