Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Permissions Needed to Count SSB Transmission Queue Messages

5 views
Skip to first unread message

zinck74

unread,
Jun 17, 2010, 7:21:22 PM6/17/10
to
Hi,
I'm trying to figure out a way for a monitoring app to count messages
in our transmission_queue. We want the app to alert us if it's over X
number of messages. As it is now, giving the account db_reader perms,
it doesn't even return an error. It returns 0, which is odd. It
seems that giving sysadmin privileges will allow a full count, but
would rather not do that. Is there some sort of low perm, a read-only
like perm, that would allow a login to count all the messages in the
transmission_queue?

Thanks,
Bill

Bill208

unread,
Jun 25, 2010, 4:27:11 PM6/25/10
to
Bump...

Anyone have any info on this? Still have not resolved this. I
realized that this is a view and am guessing there is some table
behind that we don't have the right access to. But you can't script
out this view to find out what that table is.

Thanks,
Bill

Erland Sommarskog

unread,
Jun 25, 2010, 5:59:50 PM6/25/10
to
Bill208 (bke...@gmail.com) writes:
> On Jun 17, 4:21 pm, zinck74 <bkel...@gmail.com> wrote:
>> I'm trying to figure out a way for a monitoring app to count messages
>> in our transmission_queue.  We want the app to alert us if it's over X
>> number of messages.  As it is now, giving the account db_reader perms,
>> it doesn't even return an error.  It returns 0, which is odd.  It
>> seems that giving sysadmin privileges will allow a full count, but
>> would rather not do that.  Is there some sort of low perm, a read-only
>> like perm, that would allow a login to count all the messages in the
>> transmission_queue?

For many of the DMVs, the rule is that you may read data you have
permission to see or own. Thus, you will not get an error if there
is data you are not entitled to see.

Unfortunately, the Permissions section in Books Online does not give
information about the required permissions to view data, but I would
guess that you need permissions on the services, but exactly what is
not clear.

The alternative is to put the count(*) in a stored procedure, then you
can use certificate signing or impersonation to bundle the permission
with the procedure. I have an extensive article about this on my web
site: http://www.sommarskog.se/grantperm.html.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0 new messages