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.