SQL Server Login permission to read queue message count of Service Broker

SQL Server Login permission to read queue message count of Service Broker

Few weeks back, I have written one article “Find Service Broker Queue Count in SQL Server”. One of the blog reader has created the UDF I have given in that article and tried to fetch value of that UDF from his web application to find the service broker queue count but, unfortunately, It was returning 0 value in web application but if he tries to execute UDF function in SSMS itself, it used to return proper queue count.

He wrote me back to find the solution of the problem he was facing. I asked him the login he is using in SSMS as well as from application and as soon as I get this answer from him, I get to know the reason exact issue he was facing.

He was using Windows Authentication “Administrator” login while using SSMS and was using WebAppEnt SQL Server login from web application. I guessed that Administrator login would by sysAdmin and WebAppEnt login would have only required permission which my reader confirmed. I told him to give proper permission to WebAppEnt login with following script.

USE Master GO 
GRANT VIEW ANY DEFINITION TO WebAppEnt; 
GO 

As soon as He ran above given script, everything started working fine. :)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah http://Extreme-Advice.com http://www.sqlhub.com Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “SQL Server Login permission to read queue message count of Service Broker”

Comments are closed.