Why Enabling TRUSTWORTHY Is an Untrustworthy SQL Server Security Practice

If you have ever used our free tool to check SQL Server security, you may have seen the check for the “TRUSTWORTHY database owned by sysadmin” show up as one of the highest of priority items, requiring action. When we started reviewing the security permissions and configurations for our clients’ instances, we didn’t expect to find it very often since TRUSTWORTHY database setting is off by default.

Unfortunately, this has been discovered with some frequency, and when combined with a few other common practices, it presents a tremendous vulnerability to escalate privileges for both authorized users and hackers.

What does enabling TRUSTWORTHY do?

As Microsoft documentation on TRUSTWORTHY indicates, enabling this setting indicates that the SQL Server instance “trusts” the contents of the database entirely. This is rarely a good idea, because trusting all contents of a database is a lot of trust.

Based on my experience, I’d say this setting is most often enabled because someone was trying to sort out permissions for something to execute successfully, and when they enabled TRUSTWORTHY the process finally worked. Problem solved, right?

Yes, but enabling the setting can invite much bigger problems. According to the aforementioned documentation, there are two potential threats:

• You could have assemblies (CLRs) with EXTERNAL_ACCESS or UNSAFE permission settings executing code in the database they otherwise could not have done.
• You could have certain users escalating their privileges beyond what they have been assigned, including the potential to execute as members of the all-powerful sysadmin role.

Why is TRUSTWORTHY often a big vulnerability?

That first threat is a bit rare, as most folks aren’t using CLRs. And if they are, well, that’ a potential vulnerability in and of itself. But the second threat is often a big problem because of two common practices.

First, databases are often owned by members of the sysadmin role. Sometimes the owner is the ‘sa’ login. This is commonly used because it’s less problematic when copying a database from one instance to another, since every instance has the sa login. But also, when restoring a database to another instance, it can restore with the owner assigned as the principal who did the restore, and that principal is nearly always a member of the sysadmin role.

The second common practice is the common assignment of database users to the db_owner role. Users often need permissions to read and write data, as well as execute stored procedures and functions, and if administrators don’t know or don’t care about assigning these permissions explicitly, they will often just add them to the “db_owner” role and call the permissions good.

If you have done both of these things in a database marked as TRUSTWORTHY, and it seems a lot of folks have, here is all that needs to be done for ANY user that is in the db_owner role to magically be able to execute scripts as a member of the sysadmin role.

CREATE PROCEDURE LookMomImSysadminNow
WITH EXECUTE AS OWNER
AS...

Now anything in that stored procedure, which potentially includes changing permissions of other users, backing up or dropping databases, or using xp_cmdshell to execute commands outside of SQL Server, will be done in the context of the database owner.

Yeah, this is kind of a big deal.

How can I tell if this is a problem for me?

If you want to check if you have any databases that have TRUSTWORTHY enabled and are owned by a member of the sysadmin role, you can use this script to discover them.

SELECT db_name(database_id)
FROM sys.databases
WHERE database_id > 4
    AND is_trustworthy_on = 1
    AND IS_SRVROLEMEMBER ('sysadmin', SUSER_SNAME(owner_sid)) = 1;
If this returns any find any databases, find the members of the db_owner role with this script to see who can effectively act as a sysadmin.
USE [MyTrustworthyDatabase];
SELECT
    u.name AS 'UserName'
    , r.name as 'RoleName'
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r
    ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals u
    ON rm.member_principal_id = u.principal_id
WHERE r.name = 'db_owner';

If you find yourself in this situation, there are a few ways to mitigate a potential disaster. If it’s possible, disable the TRUSTWORTHY setting. Of at least talk with your colleagues to see why it is enabled and see if you can figure out how to assign the correct permissions without having this setting enable.

If you can’t resolve that, then see if you can change the owner of the database to a user with limited permissions that is not in the sysadmin role. And while you’re doing that, you should definitely try to remove any database users from the db_owner role who don’t need those elevated permissions.

I know this could be a headache to sort out, but it’s much less of a headache than having a database ripe for a hacker to elevate their permissions easily, allowing them to steal, delete, or encrypt data in your environment.