9

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?

enter image description here

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)

  • Its quite possible that during installation the registry counters are not installed. If user has sysadmin permission then he does not require view server state permission to run the DMV its included in sysadmin role. In light of above if no rows are returned this means perfmon counters are not installed. – Shanky Aug 27 '15 at 9:13
  • @Shanky Not always, there are 2 more possible reasons. I've personally had the registry reasons (in a hardened security environment where apparently the registry changes during the installation did not properly go through) – Reaces Aug 27 '15 at 9:33
4

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:

  1. Performance Objects and counters set-up during the SQL Server installation failed.
  2. A mixture of 64 and 32 bit platforms.
  3. Registry permissions have been skewed

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.

  1. Change the path to the BINN directory of the SQL Server instance you desire to correct.
    (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
  2. Execute unlodctr <<REGISTERED SERVER NAME>>
    For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
  3. Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
    For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
  4. Cycle the remote registry service:
    net stop "Remote Registry" then net start "Remote Registry"
  5. Force a WMI synchronization using 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:

    1. Grant Full Control permissions to the Creator Owner account.
    2. Grant Full Control permissions to the Administrators account.
    3. Grant Read permissions to the SQL Administrators on that box/Node.
    4. Grant Full Control permissions to the System account
  • this method is for SQL Server 2008 is this same for 2014 ? – AA.SC Sep 1 '15 at 14:04
  • @AA.SC Yes, the method hasn't changed AFAIK. – Reaces Sep 1 '15 at 14:06
  • @@Reaces client updated his system RTM to SP1 this has resolved the issue. – AA.SC Sep 11 '15 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.