In SQL Server, the VIEW SERVER STATE permission allows a user to view server-wide dynamic management views (DMVs) and functions. This is often useful for troubleshooting, monitoring, and performance tuning.
In this blog post, I’ll share how to check which users have the VIEW SERVER STATE permission, and granting this permission to a user.
1. Query to Check Users with VIEW SERVER STATE
Permission
To verify that the user has been granted this permission, you can query the sys.server_permissions
catalog view:
-- Check who has VIEW SERVER STATE permissions SELECT grantee.name AS UserName, permission_name, state_desc FROM sys.server_permissions AS sp JOIN sys.server_principals AS grantee ON sp.grantee_principal_id = grantee.principal_id WHERE permission_name = 'VIEW SERVER STATE';
This will return all users who have the VIEW SERVER STATE
permission. If you are using Always On Availability Groups, you should verify that these permissions have been sync’d between all SQL Replicas.
2. Granting VIEW SERVER STATE Permissions
Here is a simple T-SQL example to GRANT
this permission to a specific user:
-- Grant VIEW SERVER STATE permission for user GRANT VIEW SERVER STATE TO [username];
Replace the [username]
with the name of the Domain User or SQL login you want to grant the permission to.
To grant this permission, you will need to run as a sysadmin
.
Leave a Reply