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:
- 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
- there is a user in the database who is a member of the db_owner database role.
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.
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