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. :)