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 