Guidelines for using the TRUSTWORTHY database setting in SQL Server


Summary


You can use the TRUSTWORTHY database setting to indicate whether the instance of Microsoft SQL Server trusts the database and the contents within the database. By default, this setting is set to OFF. However, you can set it to ON by using the ALTER DATABASE statement. We recommend that you leave this setting set to OFF to mitigate certain threats that may be present when a database is attached to the server and the following conditions are true:

  • The database contains malicious assemblies that have an EXTERNAL_ACCESS or UNSAFE permission setting. For more information, visit the following Microsoft Developer Network (MSDN) website:

    CLR Integration Security

  • The database contains malicious modules that are defined to execute as users that are members of a group that has administrative credentails. For more information, visit the following MSDN website:

    Extending Database Impersonation by Using EXECUTE AS

Note By default, the TRUSTWORTHY setting is set to ON for the MSDB database. Altering this setting from its default value can result in unexpected behavior by SQL Server components that use the MSDB database.

If the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as the sysadmin group, the database owner may be able to create and to run unsafe assemblies that can compromise the instance of SQL Server.

More Information


In an Internet service provider (ISP) environment (for example, in a web-hosting service), each customer is permitted to manage their own database and is restricted from accessing system databases and other user databases. For example, the databases of two competing companies could be hosted by the same ISP and exist in the same instance of SQL Server. Dangerous code could be added to a user database when the database was attached to its original instance, and the code would be enabled on the ISP instance when the database was deployed. This situation makes controlling cross-database access crucial.
 
If the same general entity owns and manages each database, it is still not a good practice to establish a "trust relationship" with a database unless an application-specific feature, such as cross-database Service Broker communication, is required. A trust relationship between databases can be established by enabling cross-database ownership chaining or by marking a database as trusted by the instance by using the TRUSTWORTHY property. Theis_trustworthy_on column of the sys.databases catalog view indicates whether a database has its TRUSTWORTHY bit set.

Best practices for database ownership and trust include the following:

• Have distinct owners for databases. Not all databases should be owned by the system administrator.
• Limit the number of owners for each database.
• Confer trust selectively.
• Leave the Cross-Database Ownership Chaining setting set to OFF unless multiple databases are deployed at a single unit.
• Migrate usage to selective trust instead of using the TRUSTWORTHY property.

The following code sample can be used to obtain a list of databases that have the TRUSTWORTHY bit ON and whose database owner belongs to the sysadmin server role.

SELECT SUSER_SNAME(owner_sid) AS DBOWNER, d.name AS DATABASENAME
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
inner join sys.databases d on suser_sname(d.owner_sid) = p.name
WHERE is_trustworthy_on = 1 AND d.name NOT IN ('MSDB') and r.type = 'R' and r.name = N'sysadmin'

You can run the following query to determine the TRUSTWORTHY property of the MSDB database:

select name, TrustWorthySetting =
case is_trustworthy_on
when 1 then 'TrustWorthy setting is ON for MSDB'
ELSE 'TrustWorthy setting is OFF for MSDB'
END
from sys.databases where database_id = 4
 

If this query shows that the TRUSTWORTHY property is set to OFF, you can run the following query to set the TRUSTWORTHY property to its default value.

ALTER DATABASE MSDB SET trustworthy ON
go

The following table provides more information on the products or tools that automatically check for this condition on your instance of SQL Server and the versions of the SQL Server product that the rule is evaluated against.

     

Rule software

Rule title

Rule description

Product versions against which the rule is evaluated

System Center Advisor

SQL Server msdb system database has trustworthy option set to OFF

System Center Advisor checks whether the database option Trustworthy is set to OFF for the msdb system database for this instance of SQL Server. If it is set to OFF, then advisor generates an alert. Review the information provided in the “Information Collected” section of the advisor alert and follow the resolutions that are provided in this article.

SQL Server 2008
SQL Server 2008 R2

SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

Trustworthy Bit

The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides rule to detect when a database has the TRUSTWORTHY property set to ON and whether the database owner is member of the sysadmin server group. If you run the BPA tool and receive a warning that is titled "Engine - Trustworthy Bit," we recommend that you run the query that is listed in the previous section to identify the database that has the TRUSTWORTHY bit set and that you consider changing the setting to OFF.

SQL Server 2008
SQL Server 2008 R2

SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

Trustworthy Bit

The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides rule to detect when a database has the TRUSTWORTHY property set to ON and whether the database owner is member of the sysadmin server group. If you run the BPA tool and receive a warning that is titled "Engine - Trustworthy Bit," we recommend that you run the query that is listed in the previous section to identify the database that has the TRUSTWORTHY bit set and that you consider changing the setting to OFF.

SQL Server 2012