SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Another Reason to Avoid Cross-Database Ownership Chaining

This past weekend we were moving database files around because we added new LUNs to an existing production cluster. We went at the old tried and true way, we detached the databases, moved the files, and re-attached the databases. That seemed to work well and we thought everything was okay. We found out on Monday we were wrong.

The application that was affected was Microsoft's Office Communications Server (OCS). We had detached and re-attached all the user databases, including the ones belonging to OCS. But none of us realized it had cross-database ownership chaining set for two of its three databases (and these are required):

  • rtc
  • rtcdyn

So naturally, Monday morning they started experiencing issues.Once we understood what was going on it was easy to alter the databases and fix the settings. Why would detaching the databases cause the settings to be lost? In truth, the options are stored in the master database. In SQL Server 2005/2008 you can see the values in the catalog view, sys.databases. If you do an EXEC sp_helptext 'sys.databases' you can see how the information is actually stored. But it's not stored with the database itself. It's in master. So when the sp_detach_db stored procedure is executed, that row is removed. And as a result, the settings that were in place are gone, too. This is a good reason to use ALTER DATABASE if you're on 2005 or 2008 instead if you're remaining on the same instance.

Now this means any of the database options are similarly affected, so I am being a little unfair to cross-database ownership chaining by singling it out. However, cross-database ownership chaining is one of those options, from a security perspective, you only turn on when you have to. And in most cases you can architect your applications where it's not needed. I know there are exceptions; I've built one myself. Now with respect to OCS, I'm not so sure OCS truly had to have it, but that's the way it rolled to production release, so we'll have to live with it.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by ALZDBA on 20 February 2009

Nice summary.

Probably MS should at least give a warning when detaching a db which has cross db ownership enabled.

Indeed using the alter database methode is less prone to "human" errors ....


Posted by RichardB on 20 February 2009


I have found various problems associated with detach/reattach db - or for a quicker turnaround logship and rename - where the db_id gets changed.  In some data access layers this is cached instead of the dbname for some reason - causing all sorts of issues.

Leave a Comment

Please register or log in to leave a comment.