SQL Server 2012 grant access to sys.dm_os_ring_buffers from ASP.net

Question

I want to access a system table from within my ASP.Net application. Currently the application connects to the database StudentPortal with a user account of: WebAppClient.

When I try to run the following query logged in as WebAppClient I get the error:

The user does not have permission to perform this action.`

How can I grant access so that my user account can query that system table?

This is my full query:

SELECT TOP 1
     100 - r.SystemIdle AS CPU
FROM (
    SELECT
        rx.record.value('(./Record/@id)[1]', 'int') AS record_id,
        rx.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
    FROM (
        SELECT CONVERT(XML, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE
            ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND
            record LIKE '%<SystemHealth>%') AS rx
    ) AS r
ORDER BY r.record_id DESC

score 2 · Accepted Answer · edited Mar 03 '14 at 21:05

2

The user must be granted the "View Server State" permissions to view sys DMVs.

Here is the MS KB article.

Actual Satement: GRANT View Server STATE TO <user>

edited Mar 03 '14 at 21:05

Chris Kooken

32,730
15
85
123

answered Feb 25 '14 at 04:57

Dave C

7,272
1
19
30

SQL Server 2012 grant access to sys.dm_os_ring_buffers from ASP.net

1 Answers1