1

I am reading about cross db ownership chaining Server Configuration Option

It talks about how and why to use (or not) this option and says

Setting cross db ownership chaining to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting.

It also offers this code to check. 

SELECT is_db_chaining_on, name FROM sys.databases;

According to Server Configuration Options (SQL Server) the defualt value for 'cross db ownership chaining' is 0 (zero)

When I run it on my servers, old, new or whatever they all seem have it enabled on master, tempdb, and msdb. I checked a few from SQL 2012 to SQL 2017, Enterprise and Standard editions.

I did find that it where it is set to on by default but not why.

If it is bad, why is it on by default on system databases?

Why am I not finding anything talking about why it is on by default on system databases?

Improve this question
3
  • Can you check The model database? Everything Is mimicked from this database
    – clifton_h
    Oct 26, 2018 at 13:10
  • @clifton_h It is not on for the model database, only the other three system databases. Per the links in my question, these are activated by default when built. Other then msdb, it not even an option to alter after build.
    – James Jenkins
    Oct 26, 2018 at 15:43
  • Well if that is “activated” per database as opposed to the server, the model database IS a culprit. All default values are copied based on the model database. But I see what you mean
    – clifton_h
    Oct 26, 2018 at 16:41

1 Answer 1

Reset to default
0

I have continued to search for reasoning behind this. Everything I find is some version of

These system databases must have cross-database ownership chaining turned on in order to function properly source

Clearly the system databases; master, tempdb, and msdb are interdependent. Presumably early in SQL development, some microsoft developer made the choice to use cross-database chaining to solve access issues, and it has never been updated.

Another of of those things that is Microsoft recommends not be used, but they are dependent on it.

Improve this answer

Your Answer

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

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