Why is the MSDB database TRUSTWORTHY?

The TRUSTWORTHY setting can be rather dangerous if you aren't careful and except for specific circumstances the recommendation is to keep it turned off. However by default the MSDB database has TRUSTWORHTY set ON by default. I'm curious why?

I've read this entry in BOL


Note By default, the TRUSTWORTHY setting is set to ON for the MSDB database. Altering this setting from its default value can result in unexpected behavior by SQL Server components that use the MSDB database.

But I'm curious about specifics. Why specifically does MSDB need TRUSTWORTHY turned on? What functions use it?

There are dozens of objects in msdb that reference the master database.

If msdb were not marked as TRUSTWORTHY, then users would need permission to both the msdb object they are interacting with, plus the master object that is being referenced.

For example, msdb users who get permissions via the SQLAgentUserRole database role are granted execute on msdb.dbo.sp_enum_sqlagent_subsystems. The call stack for that procedure eventually hits master:

* msdb.dbo.sp_enum_sqlagent_subsystems
    * msdb.dbo.sp_enum_sqlagent_subsystems_internal
        * master.dbo.xp_instance_regread

If msdb is not marked as TRUSTWORTHY, then users who are part of the SQLAgentUserRole database role would ALSO need execute permission on master.dbo.xp_instance_regread.

Technically speaking, it is probably possible to remove the TRUSTWORTHY setting in msdb and instead grant specific permissions in master. However, those required permissions are neither documented nor supported.

StackExchange Database Administrators Q#133325, answer score: 3