Why is the MSDB database TRUSTWORTHY?
Question
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?
asked 2016-03-24 by Kenneth Fisher
Answer
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.
answered 2016-03-28 by Andy Mallon