We came across this little nugget the other day. There is a vendor application that likes to create databases on the same drive and override the default file locations we have defined. Yeah, I know, hard to believe yet another vendor product can go out of their way to take a perfectly good server and screw it up somehow. Anyway, this was a wonderful learning opportunity for myself and our team.
From the BOL:
Generally, attaching a database places it in the same state that it was in when it was detached or copied. However, in SQL Server 2005 and later versions, attach-and-detach operations both disable cross-database ownership chaining for the database.
Oh. Well. Thanks, I guess. Not sure why you set this one property apart from all the others. I mean, what about TRUSTWORTHY? Oh…looks like you do that as well, huh?
You can see this for yourself, just cut and paste the following into SSMS and run against an instance of MS SQL that has the AdventureWorks database. You will probably need to redefine where the data and log files are for the AdventureWorks database in the part below that does the attach.
–set the chaining to be on
ALTER DATABASE AdventureWorks
SET DB_CHAINING ON
GO
ALTER DATABASE AdventureWorks
SET TRUSTWORTHY ON
GO
–examine the option, 1 means it is enabled
SELECT is_db_chaining_on, is_trustworthy_on
FROM sys.databases
WHERE name = ‘AdventureWorks’
GO
–detach the database
USE master
GO
EXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks’
GO
–attach the database
USE [master]
GO
CREATE DATABASE [AdventureWorks] ON
( FILENAME = N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf’ ),
( FILENAME = N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Log.ldf’ )
FOR ATTACH
GO
–examine the option, 0 means it is disabled
SELECT is_db_chaining_on, is_trustworthy_on
FROM sys.databases
WHERE name = ‘AdventureWorks’
GO
Now, go one step further and do a backup and restore. Turns out that these options get turned off as well when restoring, so this is not unique to a reattach.
I would think this has to do with the database ID. I’m sure you don’t get the same ID when you restore/attach/adopt/add in/create a “new” database. Seems to me that ID would be used in system tables for chaining and so on. so to me this makes sense, and is the behavior I would expect.
oh sure…that’s what you expect. but i expect that if i restore my database that had chaining on originally, that it would remain on after a reattach or restore.
would it not be possible for the attach/restore operation to (1) recognize the option is set and then (2) update the system tables for the new db id?
I’m not sure that the detach/reattach issue surprises me, but the fact that it gets reset on a restore. For me the whole point of a restore is so my application(s) will work the same after a disaster.
to be fair, i did a backup, dropped the database, then did a restore and found it reset. so, as buck mentions, it could be a result of the new db id.
i just did another test. i enabled the options, did a dump, disabled the options, did a restore, and found the options disabled.
i then enabled the options and did a restore to the dump i had just taken, and the options were still enabled.
i did not drop the database, so it would appear that whatever the options are set to for that db id, the options will persist for a same-server restore. i would expect that if you were recovering in the event of a disaster the options would persist for you as well. but if you are restoring to a different server then you would probably need to remember to have these enabled.
all the more reason to be capturing the info in the sysdatabases tables every night, i guess.
Thank You Tom for putting this info as a blog and giving us more insight to it.
Thomas – I’m sure there would be a way to do that. The only issue, I think, would be not just the DBID, but then all of the security account GUIDS as well. And you couldn’t really key off of the database name to re-establish the chains and so on, because you can re-name the database in-flight. So if you don’t have the ID to match up against, and you can’t rely on the database names to re-tie everything, I’m not sure what other attribute to use to keep those options handy.
I think the point is that it is something to keep in mind when moving or restoring a database. My suspicion is that there are possibly other security related issues to keep in mind as well, such as orphanded users, certificates, aliases and so on.
Thanks –
Buck
Ah yes I have seen this cheeky little issue before too when working with log shipping. This was when I was working on a project to offload reporting from a production server.
When setting up the secondary server (in standby mode) I restored the database from a backup of the primary and in doing so noticed that the CLR capabilities could not be accessed. This was because the TRUSTWORTHY database property was being reset to the default of false on the secondary database in spite of the property being set to true on the primary.
Of course the only way to change the property on the secondary was by bringing the database online which in turn breaks the log shipping configuration. In time a more robust Replication solution was devised and proved to be more suitable in this particular scenario but still, this is a sneaky little issue.