Incorrect brokerage accounting and database
I have a SQL Server 2008 R2 implementation with Service Broker enabled for a .Net / IIS website running in the same box.
It does not throw an error when the global.asax application_startup event occurs, however the event log is spammed every second with
[DBO]. [SqlQueryNotificationStoredProcedure-e6946263-93b8-445e-9d92-6fbd49a4b089] start in queue 'XXXXXX.dbo.SqlQueryNotificationService-e6946263-93b8-445e-9d92-6fbd49a4b089 database owner in the database SID is the following the owner of the database recorded in the database 'XXXXXXX'. You should correct this situation by resetting the owner of the database 'XXXXXXX' using the ALTER AUTHORIZATION statement. '
Also, the service broker does not send messages correctly (for SqlCacheDependency) - it mostly fails.
I ran the following query and identified the property mismatch:
SELECT
SUSER_SNAME(d.owner_sid) AS OwnerName
,d.owner_sid AS OwnerSID
,dp.sid AS DboUserSID
,SUSER_SNAME(dp.sid) AS DboUserMapping
FROM sys.databases AS d
JOIN sys.database_principals AS dp ON
dp.name = 'dbo'
WHERE d.database_id = DB_ID();
OwnerName: usrAAAAA
OwnerSID: 0xAAAAA
DboUserMapping: sa
DboUserSID: 0x01
In most places I've seen, I suggest using ALTER AUTHORIZATION to explicitly specify "sa" as the owner of the database. However, I'm not sure if it should be set to sa
or usrAAAAA
, and I'm not sure if there are any likely consequences (what else can be broken, if any).
Any help on this would be greatly appreciated.
source to share
Setting the database owner to "sa" is most similar to work without causing other problems, but it can cause some security issues as described here: Is there a reason I shouldn't set my db owner to sa? ...
Note also that depending on the requirements of your application and how the accounts are used, if you delete 'usrAAAAA' as a dbo, you may need to add them back to the DB as a user, and you may also need to grant them permissions ( for example "db_owner"). If this is the case, and you have configured your database for TRUSTWORTHY
(as is often required with Service Broker-related), then you should probably set the owner instead of "usrAAAAA" and try to get it working.
source to share