Search code examples
asp.netsql-serversql-server-2012database-permissions

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


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

Solution

  • 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>