locked
Verifying GRANT permissions on Service Broker CONTRACT and Queue RRS feed

  • Question

  • 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.

    One observation:

    There are lots of rows (>100,000) in these tables:


    SYS.CONVERSATION_ENDPOINTS

    sys.conversation_groups

    However we are not doing any custom development on conversations, just using the SqlDependency feature.

    Thankful for any advice!

     

    Thursday, October 7, 2010 1:57 PM

Answers

  • Hi Theodore_J,

     

    How can I check whether the user e.g. 'databaseUser' has this REFERENCES granted on this contract? Which view/stored proc. to use?

    In order to check if this user has this permission, we could use these system views: sys.database_permissions, sys.service_contracts and sys.database_principals.

     

    How could I verify that the RECEIVE permission has been granted?

    In order to verify it this permission has been granted, we could use these system views: sys.database_permissions, sys.database_principals and sys.objects.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Sunday, October 17, 2010 8:11 AM
    Monday, October 11, 2010 9:01 AM

All replies

  • Hi Theodore_J,

     

    How can I check whether the user e.g. 'databaseUser' has this REFERENCES granted on this contract? Which view/stored proc. to use?

    In order to check if this user has this permission, we could use these system views: sys.database_permissions, sys.service_contracts and sys.database_principals.

     

    How could I verify that the RECEIVE permission has been granted?

    In order to verify it this permission has been granted, we could use these system views: sys.database_permissions, sys.database_principals and sys.objects.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Sunday, October 17, 2010 8:11 AM
    Monday, October 11, 2010 9:01 AM
  • Open Microsoft SQL Server Management Studio > Expand the tree > Your Database > Security > Users > Expand list of users

    Right-click on the user you suppose you have granted the RECEIVE permission

    Click on Properties > Securables

    Under Securables select your Queue

    Under Permissions for YourQueue you should see the RECEIVE permission you granted before.

    Regards

    Doctor Wu




    • Proposed as answer by Doctor Wu Wednesday, May 16, 2012 10:06 AM
    • Edited by Doctor Wu Wednesday, May 16, 2012 10:08 AM
    Wednesday, May 16, 2012 10:03 AM