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];