Detaching then Attaching a Database Loses the Database Settings

In my last post about moving database files, I touched on the point when you detach and then attach a database, you lose some of the database settings. This is a real gotcha that many people don’t realise…until they’ve done it and wonder why things become broken.

The database settings that get changed when you detach then attach a database are:

• The database owner changes to the user running the attach command
• Read only databases change back to read/write
• Trustworthy gets disabled
• Cross Database Ownership Chaining gets disabled
• Service Broker gets disabled

I’ll demonstrate this by enabling these database related features, then detach and attach the database. Here I will use a database called AttachDetachTest. It has a database owner called Jacinta, and I will enable all of these four settings we are interested in. The query below shows the settings before I detach it:

I now detach the database and instantly attach it again, with the files in the same location:

Now lets check the database settings again:

Wow! The database owner changed to a different login – the login which I ran the attach command under. You never know, that previous database owner could have been the way your application got it’s permission to the database, meaning your application can no longer connect to the database! Attaching the database also set it back to read/write and disabled trustworthy, cross database ownership chaining and service broker. I have no doubt, that many people will quickly come running in your direction pretty soon after this change if you required any of these settings to remain the same.

Ultimately, the best way to avoid this is to not detach then attach a database if you’re keeping it on the same SQL Server instance. Instead take the database offline, then set it online when you’re finished moving it. This method keeps all the database settings. It’s because when the database is detached, the database is completely getting removed from the SQL Server instance, and when it’s attached, it’s essentially creating a new database. Whereas setting it offline means SQL Server still knows about the database and all it’s settings, so nothing has to change.

If you absolutely must use the detach and attach method (I’m sure there are some reasons out there), then you should take a copy of all the settings before you detach the database, so that you can quickly change the settings back to how they should straight after you attach the database. The query you could use for this is:

declare @database_name nvarchar(128) = N'AttachDetachTest' -- Update this to the database you will detach
declare @sql_commands nvarchar(400) = N'';
select 
@sql_commands += 
	N'use ' + quotename([name]) + N'; alter authorization on database::' + quotename([name]) +
		N' to [' + suser_sname(owner_sid) + N'];' + nchar(13) +
	case is_trustworthy_on 
		when 0 then N'use [master]; alter database ' + quotename([name]) + N' set trustworthy on;'  + nchar(13) 
		else N'' end +
	case is_db_chaining_on 
		when 0 then N'use [master]; alter database ' + quotename([name]) + N' set db_chaining on;'  + nchar(13) 
		else N'' end +
	case is_broker_enabled 
		when 0 then N'use [master]; alter database ' + quotename([name]) + N' set enable_broker with no_wait;'  + nchar(13) 
		else N'' end +
	case is_read_only 
		when 0 then N'use [master]; alter database ' + quotename([name]) + N' set read_only with no_wait;'  + nchar(13) 
		else N'' end
from sys.databases where [name] = @database_name
print @sql_commands;

Which looks like this when you run it:

Save the commands that it outputs to the messages tab so that they can be run straight after you attach the database. Better yet, copy those commands and add them to the same script to attach the database. Like so:

The query before and after the attach/detach shows that all the settings get applied instantly after the database gets attached as if nothing happened.