AKAWN® LIMITED

SQL Server specialists

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:

 

 

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.

 

 

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

 

 

 

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

 

 

 

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

 

 

Category: Security