No permission to access SQL server perfmon counters

Question

My DBA team doesn't have access to SQL server 2005 standard performance counters.

What is the required permission that they should be granted? I found some registry entries, but they already have permissions there. they're not OS administrators.

Answer 1

Are you speaking of the sys.dm_os_performance_counters DMV ?
You need to grant the SQL Server login the VIEW SERVER STATE permission.

GRANT VIEW SERVER STATE to [performance_user]

This will also give the user account access to all other DMV's, & may not be granular enough. Simply granting SELECT permission on the view doesn't work!!

If you want to do this outside of SQL Server & use the Performance Monitor mmc, then look at this MSKB article. You will need to modify some file level permissions & registry level permissions on the server:

If the systemroot is on an NTFS partition, you must have at least Read access to the following two files:

• %SystemRoot%\System32\Perfc009.dat
• %SystemRoot%\System32\Perfh009.dat

You must have at least Read access to the following registry keys on the remote computer:

• HKEYLM\SYSTEM\CurrentControlSet\Control\SecurePipeServers\winreg
• HKEYLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib

You must have Full access to the following registry key on the remote computer:

• HKEYLM\SYSTEM\CurrentControlSet\services\servicename\performance

Answer 2

Is the remote registry service running on the affected machines? Also, add the domain user accounts for the DBA's (or better yet, create a domain group for the DBA's) and add the domain users or group to the local Performance Monitor Users group.