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