IF exists (select DB_Name() where DB_Name() like '%NgsCore%')
AND (select is_broker_enabled from sys.databases where [name] =
DB_Name()) <> 1
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'ALTER DATABASE ' + DB_Name() + ' SET ENABLE_BROKER WITH
ROLLBACK IMMEDIATE ;'
EXEC sp_executesql @SQL
END
GO
CREATE ROLE NGSSQLDependency AUTHORIZATION db_owner
GO
GRANT CREATE PROCEDURE TO NGSSQLDependency;
GO
GRANT CREATE QUEUE TO NGSSQLDependency;
GO
GRANT CREATE SERVICE TO NGSSQLDependency;
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO NGSSQLDependency;
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO NGSSQLDependency;
GO
GRANT REFERENCES ON
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO NGSSQLDependency;
GO
GRANT SELECT ON dbo.ApplicationInstance TO NGSSQLDependency;
GO
But when the GRANT RECEIVE statement runs, strange things occur. First when
I go back to SSMS and look at the role I first get an error msg "Value does
not fall within the expected range. (SqlMgmt)" when I select the Securables
page.
Also any Securable schemas that were there before running the SP are gone.
This occurs immediately after executing the GRANT RECEIVE statement.
What is interesting is that if I run exec sp_helprotect NULL,
'NGSSQLDependency', the results seem to indicate everything is there.
I do not get any error msgs when I run script above; so am not sure what is
going on.
Documentation and BOL on Service Broker is less than helpful. I would
appreciate any ideas.
Cheers,
Mike