master User Objects
Overview
Every instance that has been run by a person rather than by an installer has objects in master that Microsoft did not put there.
A monitoring procedure somebody pasted in for an afternoon in 2019. A script kit. A table of server names that one job reads. A function three procedures in other databases call through a three part name.
None of it is in source control, none of it is in any backup except a backup of master, and a rebuild of the instance loses all of it.
Where to find it
Expand a server in the tree, expand the master database, then Master → User Objects.
The two views
| View | What it shows |
|---|---|
| What installed it | Objects grouped by what installed them, with the ones nobody recognizes grouped as installed by hand. |
| What holds space | The objects that hold space, which is a different and much shorter list: almost everything here is a procedure or a function and holds none. |
A dozen objects whose names all begin sp_Blitz are one decision somebody made, not a dozen findings, and collapsing them is what makes the ones nobody can account for visible.
The shipped flag is a claim rather than a fact
is_ms_shipped is shown as a column rather than only used as a filter.
Any sysadmin can mark an object as shipped through sp_MS_marksystemobject, and several third party installers do exactly that to keep their objects out of a scripting run.
The unfiltered view is one click away for that reason.
Reading the chart
One bar per installer, so a dozen objects from one script kit are one bar and one decision rather than a dozen findings.
The group drawn amber is installed by hand, which is the group with no kit behind it and the only one on the page that anybody has to account for.
Reading the grid
| Column | What it is |
|---|---|
| Object | The object, schema qualified. |
| Kind | Procedure, function, table, view, type or assembly. |
| Installed by | The kit the naming identifies, or installed by hand. |
| Owner | The principal that owns it. |
| Created | create_date, against which the instance build date is the reference. |
| Last changed | modify_date. |
| Rows | Row count, for the objects that have one. |
| Size | What it holds. |
| Shipped flag | is_ms_shipped, shown rather than assumed. |
The toolbar
| Control | What it does |
|---|---|
| What installed it / What holds space | The two views. |
| Backup and rebuild | Opens master Backup and Rebuild Readiness, which is what decides whether any of this survives. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.objects in master |
Every object, its type, create and modify dates, and is_ms_shipped. |
sys.schemas, sys.server_principals |
The names and owners. |
sys.dm_db_partition_stats |
Rows and space for the tables. |
sys.sql_modules |
The definitions, for the drill through. |
sys.assemblies |
CLR assemblies installed in master. |
Related reports
| Report | Why you would go there |
|---|---|
| master Backup and Rebuild Readiness | Whether any of this is in a backup. |
| master Footprint | Why master is larger than it should be. |
| master Startup Procedures | A hand written startup procedure is also on that page. |
| Third Party Objects | The same question for msdb. |
| Schema Search | Finding the code that calls these through a three part name. |
Frequently asked questions
Why does anything live in master? Usually because sp_ procedures in master can be called from any database without a three part name. That convenience is why the objects are there and why nobody remembers them.
We are migrating. What do I lose? Everything on this page, unless it is scripted out or restored from a backup of master. A new instance starts with none of it.
An object is marked as shipped and I know we installed it. sp_MS_marksystemobject was used. The flag is a claim, which is why the page shows it rather than filtering on it silently.
One kit accounts for thirty objects. Then it is one decision, and the grouping is doing its job. The rows worth reading are the ones under installed by hand.