9

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?

3
  • 2
    Just a quick thought without research for WHY would be so the SQL Agent service can interact with all DBs on an instance, to use DB mail for notifications, other external resources; paths, AD objects, etc. within SQL Agent jobs. Mar 24, 2016 at 23:59
  • 1
    @JUICED_IT also SQL Agent job history, log shipping monitor history, SSIS packages, Database Engine Tuning Advisor data, and Service Broker queue data, and backup information. That's a lot of things that you want standard behavior from. sqlperformance.com/2015/07/sql-maintenance/msdb Mar 25, 2016 at 0:02
  • I suspect it is required for anything that uses cross-database calls. You should turn TRUSTWORTHY to OFF for msdb to see what stops working and you will have at least part of the answer :). I figured BACKUP would have issues but I just tried and it succeeded. Mar 25, 2016 at 0:23

1 Answer 1

3

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.

5
  • You are probably right but if so then it is equally important that the owner of MSDB have those permissions. Mar 28, 2016 at 15:30
  • @KennethFisher Probably? I don't usually mess with the database owner for msdb, so I'm not sure. I've always just left it as sa. I legitimately hadn't thought of that.
    – AMtwo
    Mar 28, 2016 at 16:17
  • Ahh but if I have the right permissions in MSDB I can use that sa access in combination with TRUSTWORTHY to take over your server :) Mar 28, 2016 at 16:34
  • True. But this is why access to system DBs should be carefully restricted. If you're not a DBA, you don't need to get into a system database.
    – AMtwo
    Mar 28, 2016 at 17:28
  • Could the same argument be made for SSISDB? That it must be trusworthy and it's a system database with limited access?
    – Zikato
    Aug 3, 2020 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.