/********************************************************************************************
Run the script one by one

********************************************************************************************/
CREATE QUEUE NameChangeQueue;
 
GO
 
 
CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

GO

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO keyhouse;

GO

ALTER DATABASE [Keyhouse_pro] set enable_broker with rollback immediate;
 
GO

ALTER AUTHORIZATION ON DATABASE::Keyhouse_pro TO [Keyhouse];

GO

SP_CHANGEDBOWNER 'keyhouse'
 
GO

/*	<<<<<<<<<<<<<<<<<<<<--------------- BELOW QUERY FOR ONLY DEBUGGING PURPOSE ---------------------->>>>>>>>>>>>>>*/


/*
sys.transmission_queue contain if any error occur due to dbo. permission.
*/
SELECT * FROM sys.transmission_queue

GO

/*
Check the owner of your user database as recorded in sys.databases and sys.database_principals:
*/

SELECT owner_sid, SUSER_SNAME(owner_sid) AS dbowner
FROM sys.databases
WHERE name = 'keyhouse_pro';

SELECT name,sid, SUSER_SNAME(sid) AS dbo
FROM Keyhouse_pro.sys.database_principals
WHERE name = 'dbo';

--The SIDs should match.  If not, you can change the owner with ALTER AUTHORIZATION: