If you’ve ever restored/attached a database and stuff doesn’t work e.g. Service Broker, yes some database settings are not automatically re-set when a database is restored or re-attached.
The following code will help generate these settings to apply after a database restore/attach.
Of course you’ll need to have run this query before you remove the database(s) and only run the appropriate generated code section(s) for the restored/attached database(s).
SELECT 'PRINT ''[' + [name] + '] is_trustworthy_on enabled''; ALTER DATABASE [' + name + '] SET TRUSTWORTHY ON;' AS [run_me] FROM sys.databases WHERE is_trustworthy_on = 1 AND [name] <> 'tempdb'
UNION
SELECT 'PRINT ''[' + [name] + '] is_broker_enabled enabled''; ALTER DATABASE [' + name + '] SET ENABLE_BROKER WITH NO_WAIT;' FROM sys.databases WHERE is_broker_enabled = 1 AND [name] <> 'tempdb'
UNION
SELECT 'PRINT ''[' + [name] + '] is_db_chaining_on enabled''; ALTER DATABASE [' + name + '] SET DB_CHAINING ON;' from sys.databases WHERE is_db_chaining_on = 1 AND [name] NOT IN ('master','tempdb')
UNION
SELECT 'PRINT ''[' + [name] + '] db_owner changed to [' + SUSER_SNAME(owner_sid) + ']''; ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [' + SUSER_SNAME(owner_sid) + '];' from sys.databases WHERE owner_sid <> 0x01 AND [name] <> 'tempdb'
