“Database master key required”… messages in the SQL Server log

When you restore a database, TRUSTWORTHY is automatically set to OFF. For Service Broker, if you don’t use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON.

Try


ALTER DATABASE MyDB SET TRUSTWORTHY ON

And in my ever so helpful tradition here is a script that will generate the script for all databases that have trustworthy turned off, are not system databases and not readonly.


select 'ALTER DATABASE ' + name + ' SET TRUSTWORTHY ON' from sys.databases
where is_trustworthy_on = 0
and is_read_only = 0
and database_id > 4

Advertisements
About

IT professional with wider interest in technology

Tagged with: ,
Posted in sql, tsql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: