I have cloned a virtual copy of Win2003 server that has SQL2005 already installed. The new server is planned to be disconnected from the domain of the original server and run separately.
In SQL2005 I dropped all databases and restored my database that has service broker already enabled, the restored database was running very well on another server inside my old domain "AWQAF", this database also has a notification service running on top of a message queue called "messageQueue".
Service broker then didn't working as supposed or as it should to work like.
I tried to check the status of the service broker using the following statement:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'AfaqCMS';
It gives me 0 result, a mean of service broker is disabled.
So, I tried to enable service broker using the following statement:
ALTER DATABASE [AfaqCMS] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
By trying to SEND/RECIVE messages using the following statements, I got dialog created without messages transmission:
DECLARE @NotificationDialog uniqueidentifier;
SET QUOTED_IDENTIFIER ON;
BEGIN DIALOG CONVERSATION @NotificationDialog
FROM SERVICE ChangeNotifications
TO SERVICE 'ChangeNotifications'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF;
SEND
ON CONVERSATION @NotificationDialog
MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (N'CommentHits');
SELECT @NotificationDialog;
RECEIVE * FROM ChangeMessagesQueue ;
By checking transmission queue of the system I found some errors around the original owner of the restored database.
SELECT * FROM sys.transmission_queue
A filed called "transmission_status" is containing the following exception:
An exception occurred while enqueueing a message in the target queue.
Error: 15404, State: 11.
Could not obtain information about Windows NT group/user 'AWQAF\adm_walghool', error code 0x534.
By deleting 'AWQAF\adm_walghool' from all locations it can be existed in, I got another status:
An exception occurred while enqueueing a message in the target queue.
Error: 15517, State: 1.
Cannot execute as the database principal because the principal "dbo" does not exist,
this type of principal cannot be impersonated, or you do not have permission.
Now I realized that it is the database owner that prevents service broker from working properly, So I run the following statement to get every things working successfully again:
ALTER AUTHORIZATION ON DATABASE::[AfaqCMS] TO [SA]
Anthother statement can be used also:
USE AfaqCMS
GO
EXEC dbo.sp_changedbowner @loginame = N'zakauser', @map = false
