master Database Properties

Overview

sys.databases holds around sixty properties per database, and a page that printed them would be a wall nobody reads.

What somebody actually wants to know is which databases disagree with the rest.

So this page works out the house standard for each property, which is simply the most common value across the databases on this instance, and then shows only the cells that differ from it.

Twelve databases with page verify CHECKSUM and one with TORN_PAGE_DETECTION produces one row and one cell.

The master Database Properties page: the databases that disagree with the rest
The whole page. Twenty one databases, fifteen of them differing somewhere, ranked by how far each one is from the rest, and one grid row per differing setting.

Where to find it

Expand a server in the tree, expand the master database, then Master → Database Properties.

A handful are reported whether they drift or not

Because the house standard can itself be wrong:

Setting Why it is always reported
Auto shrink on A database that shrinks itself refragments itself, forever.
Auto close on Every first connection pays for the database to open again.
Page verify not CHECKSUM The other settings do not detect a torn page reliably.
Owner whose login no longer exists An orphaned owner SID, which breaks ownership chaining and several operations.
TRUSTWORTHY See below.

TRUSTWORTHY is only interesting in combination

TRUSTWORTHY with an owner who is sysadmin is a path from db_owner in that database to sysadmin on the instance, and the two columns mean nothing apart.

So the page reports them together, and says which combination it found.

The column list is built by version

sys.databases gained columns in almost every release since 2005, and naming a 2019 column on a 2016 instance fails the whole batch at compile time, which no TRY around the query catches.

So the column list is assembled from the instance’s version before the batch is sent, rather than written out once.

Reading the chart

The bars: one per database, ranked by how far it is from the house standard
Each bar is one database and its distance from the way the rest of this instance is set up, with the count of differences behind it on the right.

One bar per database, longest first. The length is how far that database is from the house standard, and the label on the right is the number of settings behind it.

The bars draw the top thirteen. The grid holds every difference on the instance, which is why a database with one difference still has a row there.

Reading the grid

The grid: one row per differing setting
One row per difference: what this database has, what everywhere else has, and one sentence on what the difference actually does.
Column What it is
Database The database that differs.
Setting The property.
Here What this database has.
Everywhere else The house standard, and how many databases hold it.
Why it matters One sentence on what this difference does.

A row exists only because there is a difference, so an empty page is a consistent instance and is an answer rather than a failure.

The toolbar

Control What it does
File map Opens master File Map.
Security posture Opens the Security Posture report.
Change ledger Opens master Change Ledger.

Where the data comes from

sys.databases, with the column list built from the instance version, plus sys.server_principals to resolve each database’s owner_sid.

Report Why you would go there
master File Map Every file of every database, with the growth settings.
Security Posture Where TRUSTWORTHY and an orphaned owner belong in the wider picture.
master Change Ledger When somebody changed one of these.
Database Overview The one database view of the same settings.
Failover Compatibility Compatibility level and collation differences across the estate.

Frequently asked questions

The page is empty. Every database on this instance agrees with every other, and none of the always-reported settings fired. That is the good answer.

Why is the house standard just the most common value? Because it is the only definition that needs no configuration and cannot be wrong about this instance. The always-reported list covers the case where the common value is itself the problem.

TRUSTWORTHY is on and the page did not flag it red. Look at the owner. TRUSTWORTHY with a non-sysadmin owner is a much smaller thing than TRUSTWORTHY with a sysadmin one, and the page reports the combination rather than the flag.

My 2016 instance shows fewer settings than my 2019 one. Because sys.databases has fewer columns there. The list is built per instance for that reason.