Set TRUSTWORTHY On smacks down service broker from working

Within each database there exists a dbo User. This User (Database-level) always exists, but the SID (Security IDentifier) that it maps to is not always the same; it will map to whatever Login (Instance-level) is specified either when creating the Database of when altered to have a new "Database Owner". The dbo User is one of the entries in sys.database_principals.

When initially setting the Database Owner during CREATE DATABASE, or when changing it later, the SID of the "owner" is not only placed into sys.database_principals, but is also recorded in master.sys.databases. If the Database never leaves the Instance in which it was created, there should never be a mismatch between the SID values in sys.database_principals and master.sys.databases. But, if the Database is ever restored or attached to (or from) another Instance, then it is possible that the SID values won't match. You can check the values in both places using the following queries:

USE [tempdb]; -- Change to whatever DB you want to check

SELECT      msd.owner_sid,
            msp.[name]
FROM        [master].[sys].[databases] msd
INNER JOIN  [master].[sys].[server_principals] msp
        ON  msp.[sid] = msd.[owner_sid]
WHERE       msd.[database_id] = DB_ID();

SELECT sdp.[sid]
FROM   [sys].[database_principals] sdp
WHERE  sdp.[name] = N'dbo';

Now, by default, TRUSTWORTHY is set to OFF and permissions on operations involving Impersonation (i.e. EXECUTE AS) are confined to the database where the operation was executed from. While impersonating a DB User, attempting to access another DB (or even Server/Instance-level resources, I believe), SQL Server will assume that the current DB User's SID (i.e. the one being impersonated) has a matching Login so it can take on those permissions. This is blocked when TRUSTWORTHY is OFF, but setting it to ON lifts the Database-level quarantine and allows Impersonation to extend beyond the initial Database. The SID of the dbo User might certainly exist as a Login, but if it is not the same SID that is mapped as the database owner in sys.databases, then that is a clear indication that something is not right (and most likely that that DB came from another Instance), and that there might possibly be malicious intent in the requested operation.


This can happen when you restore a database that was created on another server, and the database owner login account doesn't exist or has a different SID on the server you restored to.
Choose a login that you are comfortable with owning the database, and execute this:

ALTER AUTHORIZATION ON DATABASE:: [Database Name Here] TO [Login Name];