I’ve got a database running on a SQL-Server 2012 DBMS. Consider that the server running SQL Server is not part of the network domain.
When I execute this sql command
ALTER DATABASE myDatabase SET TRUSTWORTHY ON;
a service broker queue (early setted and working) receives messages but the activeted stored procedure associated to the queue doesn’t start. Messages continue to flow in to the queue, but nothing else happens.
In the SQL SERVER log I’ve found this message
The activated proc ‘proofSchema.mySP’ running on queue ‘proofSCHEMA.myQueue’ output the following: ‘The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘myDatabase’. You should correct this situation by resetting the owner of database ‘myDatabase’ using the ALTER AUTHORIZATION statement.’
If I run this same configuration on a machine inside a network domain, everything works.
I can’t understand what is happening and why trustworthy crush with servi broker queue.
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
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
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
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];