I have a user on SQL Server 2014 Standard Edition (RTM)
with SYSADMIN
role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters
it returns no record.
Any idea what is wrong with permissions?
Output of @@Version:
Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on
Windows NT 6.3 (Build 9600: ) (Hypervisor)
Best Answer
If you're sure the user in question has
View Server State
(and it looks like in your screenshot he does).Then there are a number of reasons previously put into an msdn blog. Ranging from:
To resolve this we can use the same steps outlined in the guidelines for reinstalling the performance counters in a different stack-exchange post:
Using an elevated administrator command prompt perform the following steps.
BINN
directory of the SQL Server instance you desire to correct.(Ex:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn
)unlodctr <<REGISTERED SERVER NAME>>
For example:
unlodctr MSSQL$SQL2008
orSQLAgent$SQL2008
...lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example:
perf-MSSQL$SQL2008sqlctr.ini
orperf-SQLAgent$SQL2008sqlagtctr.ini
for SQLAgent. The/T
is important to load the SQL Server performance counter provider as a trusted provider.net stop "Remote Registry"
thennet start "Remote Registry"
winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the
WinPriv.exe
(you can get this from Task Manager)The following may also be required:
Make sure that the correct security permissions have been granted to the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
registry key: