Trusting the TRUSTWORTHY Property

TRUSTWORTHY is a database property change that can have far-reaching security consequences when turned ON. Let’s take a brief look at what the TRUSTWORTHY property is and if it’s worth turning on, even when it is a potential fix to your problems.

What is the TRUSTWORTHY Property?

The TRUSTWORTHY property, turned off by default, can be turned on for individual databases. When the TRUSTWORTHY property is on for a database, SQL Server will treat that database as more trustworthy. Probably a little too trusting, because it opens you up to users being able to maliciously use CLR assemblies or scripts executed using “WITH EXECUTE AS” to make changes with higher than desired privileges.

On or Off?

While Microsoft recommends against enabling TRUSTWORTHY, they make it easy enough to turn on. TRUSTWORTHY can be enabled for a database by running:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

Or disabled by changing ON to OFF:

ALTER DATABASE MyDatabase SET TRUSTWORTHY OFF;

This one-liner can make it appealing to avoid extra work for securing users and databases.

Is TRUSTWORTHY in the Room with Us Now?

One risk Microsoft specifically mentions is if TRUSTWORTHY is ON for a database with a database owner being of a high-level group like sysadmin. This is where someone may have too much access and cause you headaches. If you’re curious to find out if any databases match this criteria, Microsoft recommends running this query to check:

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';
GO

Don’t Take the Easy Route

Yes, it can be a quick fix to grant users the rights for what they need, but you need to consider the security risks instead of hitting what appears to be an “easy button.”

Something like code signing or module signing may be a better solution. For additional details, check out this DBA StackExchange post. Alternatives like this may be more work but will be more secure. You’re better off viewing TRUSTWORTHY as a last resort.

Thanks for reading!