Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx