I am setting up SqlDependency and wish to verify that the permissions have been applied correctly.
One of the commands that should be run to grant permissions is to grant references on a contract for the user:
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
How can I check whether the user e.g. 'databaseUser' has this REFERENCES granted on this contract? Which view/stored proc. to use?
The other command is similar (also required for SqlDependency) but grants permission "RECEIVE":
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber]
How could I verify that the RECEIVE permission has been granted?
I have currently used this command:
SELECT
[permission_name],
[state_desc],
[name]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.database_principals AS grantee_principal
ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE prmssn.class = 0
With this command I can see some permissions (such as CREATE PROCEDURE, CREATE QUEUE, etc.) but the above permissions related to the contract and queue do not seem to show in the results from sys.database_permissions.
Thankful for any advice!
View Complete Post