This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
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