The databases that I work with use a considerable amount of SQL-CLR functionality implemented via assemblies registered on the SQL Server instance. Recently, as we were closing up the day, we received a phone call with the news that one of our databases was down with a permissions error similar to the one shown below.

An error occurred in the Microsoft .NET Framework while trying to load assembly id nnnnn. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues.

Over the phone itself, we were immediately able to help the support teams resolve the issue by setting the TRUSTWORTHY property of our database to ON (as was required by our design). But, this incident triggered a bigger question:

What caused the TRUSTWORTHY property to be set to OFF automatically?

Of course, none of the involved administrators admitted to anything being changed on the server or on the SQL Server instance. Upon research and hanging around on the #sqlhelp community on Twitter, I was able to confirm that the TRUSTWORTHY property is reset to OFF by the following operations:

• Restoring a database
• Attaching a database to a SQL Server instance

Provided below is a very simple script that does the following:

1. Creates a database
2. Sets the TRUSTWORTHY property to ON
3. Backup the database
4. Detach the database
5. Attach the database
6. Drop the database so that it can be restored from the backup taken earlier

After the attach and the restore process, I query the sys.databases catalog view to check the value of the is_trustworthy_on column.

--Safety Check
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases AS sd WHERE sd.name = 'TrustCheck')
BEGIN
    ALTER DATABASE TrustCheck SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TrustCheck;
END
GO

--Create the database
CREATE DATABASE TrustCheck;
GO

ALTER DATABASE TrustCheck SET TRUSTWORTHY ON;
GO

--Check the database properties
SELECT 'Check after Database Creation',
       sd.name,
       sd.database_id,
       sd.create_date,
       sd.is_trustworthy_on
FROM sys.databases AS sd 
WHERE sd.name = 'TrustCheck';
GO

--Backup the database
BACKUP DATABASE TrustCheck 
TO DISK = 'C:Database FilesTrustCheck.bak' 
WITH INIT, 
     COMPRESSION;
GO

--Detach the database
EXEC sp_detach_db @dbname = 'TrustCheck',
                  @skipchecks = 'false';
GO

--Ensure that the database is no longer "available" on this instance
SELECT 'Check after Detach',
       sd.name,
       sd.database_id,
       sd.create_date,
       sd.is_trustworthy_on
FROM sys.databases AS sd 
WHERE sd.name = 'TrustCheck';
GO

USE master;
GO
--Attach the database
CREATE DATABASE TrustCheck
    ON  PRIMARY ( NAME = N'TrustCheck_Data', 
              FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATATrustCheck.mdf')
    FOR ATTACH
GO

--Check the database properties
SELECT 'Check after Database Attach',
       sd.name,
       sd.database_id,
       sd.create_date,
       sd.is_trustworthy_on
FROM sys.databases AS sd 
WHERE sd.name = 'TrustCheck';
GO

--Now drop the database explicitly to see what happens when we restore the database
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases AS sd WHERE sd.name = 'TrustCheck')
BEGIN
    ALTER DATABASE TrustCheck SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TrustCheck;
END
GO


--Restore the database
RESTORE DATABASE TrustCheck
FROM DISK = 'C:Database FilesTrustCheck.bak'
WITH RECOVERY;

--Check the database properties
SELECT 'Check after Database Restore',
       sd.name,
       sd.database_id,
       sd.create_date,
       sd.is_trustworthy_on
FROM sys.databases AS sd 
WHERE sd.name = 'TrustCheck';
GO

--Cleanup
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases AS sd WHERE sd.name = 'TrustCheck')
BEGIN
    ALTER DATABASE TrustCheck SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TrustCheck;
END
GO

The output of the query above is shown below.

Conclusion

If the TRUSTWORTHY setting was mysteriously reset to OFF, check whether the database was recently restored and/or attached.

Further Reading

• Find DMVs in SSMS/Object Explorer [Link]
• Catalog Views [Books on Line Link]
• sys.databases Catalog View [Books On Line Link]
• ALTER DATABASE {Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.