Today, a customer asks me to have look on each SQL Server instance to control if the user-database Guest is active or not. The easiest way is to go through the CMS (Central Management Servers) with a query but witch query….
The CIS (Center for Internet Security) provides guidance to secure SQL Server.
One of the points is to ensure that the CONNECT permission on the GUEST user database is Revoked within all SQL Server databases excluding the master, msdb and tempdb.
CIS give a query to verify the status of this permission with sys.database_permissions.
I suggest to use a more simple request with the value hasdbaccess from the system view sys.sysusers.
The result is the same because the value hasdbaccess is at 1 if the user has the CONNECT permission and 0 if not. I think is more readable on this way.
I use sp_MSforeachdb to go through all databases and create a temporary table to put in the result. After I just filter it on the value hasdbaccess.
Create table #temp ( database_name sysname, name sysname, hasdbaccess int ) insert into #Temp exec sp_MSforeachdb N'select ''[?]'' as database_name, name, hasdbaccess from [?].sys.sysusers WHERE name = ''guest''' SELECT * from #temp where hasdbaccess=1 SELECT * from #temp where hasdbaccess=0 drop table #temp
The most important is that model and every user database has 0.
Let the system databases master, tempdb and msdb with the connect permission to the database-user guest. It can be some issues, if you revoke it from the master or msdb.