When you think of system databases, tables, etc there are a number of things you might think of.
- Necessary to running SQL.
- Tables/views are read only.
- SYS schema.
- Built in roles are fixed.
And yes, msdb is necessary to running SQL and it absolutely is a system database. One of its primary functions is to manage the data for SQL Agent. So to start with let’s compare the list of jobs to, say, the list of databases.
sys.databases
- Read only
- Is actually a view on top of tables we can’t see.
- Part of the sys (system) schema.
dbo.sysjobs
- Can be written to directly.
- In fact you can even change the column structure!
- If you look at the sp_help output it’s listed as a user table
- Part of the dbo schema.
And lots of the tables we use in msdb are like that. The backup and restore history tables, the agent tables etc, etc. Even the roles SQLAgentUserRole, DatabaseMailUserRole etc can be changed by adding or removing (never ever do this) permissions.
I mean I know it’s a system database, but it really feels like a user database at times. Why do I care? Security. If you are granting people access to msdb you need to be extra careful even beyond db_owner.