Prev: SQL 2008 R2 is not let to login with one of login ID
Next: MS SQL Server 2005 hang
From: zinck74 on 17 Jun 2010 19:21
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
From: Bill208 on 25 Jun 2010 16:27
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


On Jun 17, 4:21 pm, zinck74 <bkel...(a)gmail.com> wrote:
> 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

From: Erland Sommarskog on 25 Jun 2010 17:59
Bill208 (bkelly3(a)gmail.com) writes:
> On Jun 17, 4:21�pm, zinck74 <bkel...(a)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, esquel(a)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

 | 
Pages: 1
Prev: SQL 2008 R2 is not let to login with one of login ID
Next: MS SQL Server 2005 hang