Is it just me or does msdb feel a lot like a user database?

Leave a comment

July 17, 2019 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: