Skip to content

SQL Server Security: Check if the guest is active on all user-databases through the CMS

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

Example:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)

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.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Stéphane Haby

Stéphane Haby

Stéphane Haby has more than 20 years of experience in Microsoft solutions, specializing in the installation, migration, best practices, and performance analysis of Microsoft SQL Server, as well as Microsoft Business Intelligence solutions (SharePoint, Office) and .NET development for the banking sector. A former Microsoft Most Valuable Professional (MVP) and early adopter of Microsoft Team System in France, he has authored several technical articles. He holds Microsoft Certified Solutions Associate (MCSA) and Expert (MCSE) for SQL Server, MCTS, MCITP for SQL Server 2008, and ITIL V3 Foundation certifications. His industry experience covers chemicals and pharma, banking, and financial services.View Author posts

182 posts since April 2011

Leave a Comment

Your email address will not be published. Required fields are marked *