In SQL Server, "SERVER STATE" refers to the operational state and performance-related information of the SQL Server instance. This includes:

• Currently running processes and sessions
• Lock information
• Performance counters
• Resource usage statistics
• Memory allocation details

The VIEW SERVER STATE permission allows users to:

-- Example: Querying running processes
SELECT 
    spid, 
    status, 
    loginame, 
    hostname, 
    blocked,
    cmd,
    cpu,
    physical_io
FROM 
    sys.sysprocesses
WHERE 
    spid > 50

Key capabilities include:

• Viewing all active sessions (including other users')
• Accessing DMVs (Dynamic Management Views) that reveal server state
• Monitoring resource contention and bottlenecks
• Identifying blocking chains

Here's how DBAs commonly use this permission:

-- Checking for blocking processes
SELECT 
    blocking.session_id AS blocking_session_id,
    blocked.session_id AS blocked_session_id,
    wait.resource_description,
    wait.wait_type
FROM 
    sys.dm_os_waiting_tasks wait
    INNER JOIN sys.dm_exec_sessions blocking ON wait.blocking_session_id = blocking.session_id
    INNER JOIN sys.dm_exec_sessions blocked ON wait.session_id = blocked.session_id
WHERE 
    wait.blocking_session_id IS NOT NULL

While useful for monitoring, VIEW SERVER STATE should be granted judiciously because:

• Exposes sensitive information about all database activities
• Can reveal performance issues that might be security-relevant
• Provides visibility into other users' sessions

For more granular control, consider:

-- Creating a custom role with limited access
CREATE ROLE ServerMonitor;
GRANT VIEW SERVER STATE TO ServerMonitor;

-- Or granting access to specific DMVs
GRANT SELECT ON sys.dm_exec_requests TO [User];
GRANT SELECT ON sys.dm_exec_sessions TO [User];

To track who's using this permission:

-- Create server audit
CREATE SERVER AUDIT ServerStateAccess
TO FILE (FILEPATH = 'C:\Audits\');
GO

-- Add audit specification
CREATE SERVER AUDIT SPECIFICATION AuditServerStateAccess
FOR SERVER AUDIT ServerStateAccess
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP);
GO

-- Enable the audit
ALTER SERVER AUDIT ServerStateAccess
WITH (STATE = ON);
GO

In SQL Server, "SERVER STATE" refers to the operational health and runtime information of the SQL Server instance. This includes:

• Current sessions and connections
• Locking and blocking information
• Performance metrics and wait statistics
• Memory usage and configuration
• Active traces and extended events

The VIEW SERVER STATE permission provides read access to dynamic management views (DMVs) and functions (DMFs) that expose server-wide state information. Key capabilities include:

-- Example: Querying server-wide performance data
SELECT * FROM sys.dm_os_performance_counters;
SELECT * FROM sys.dm_exec_sessions;
SELECT * FROM sys.dm_tran_locks;

Common scenarios where VIEW SERVER STATE is required:

-- Monitoring blocking chains
SELECT blocking.session_id AS blocking_session_id,
       blocked.session_id AS blocked_session_id,
       wait.resource_description
FROM sys.dm_exec_connections AS blocking
JOIN sys.dm_exec_requests AS blocked
    ON blocking.session_id = blocked.blocking_session_id
JOIN sys.dm_os_waiting_tasks AS wait
    ON wait.session_id = blocked.session_id;

Another example for performance troubleshooting:

-- Identifying top resource-consuming queries
SELECT TOP 10 
    qs.total_worker_time/qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY avg_cpu_time DESC;

While VIEW SERVER STATE doesn't allow modifying server configuration, it exposes sensitive information:

• All active queries and their text (potentially including parameter values)
• User login patterns and session information
• Database usage statistics

-- Grant to specific logins (not roles) when possible
GRANT VIEW SERVER STATE TO [MonitoringUser];

-- Create custom database role for monitoring if needed
CREATE ROLE [ServerMonitor];
GRANT VIEW SERVER STATE TO [ServerMonitor];

For auditing purposes, track permission assignments:

-- Check existing VIEW SERVER STATE grantees
SELECT perm.state_desc, prin.name
FROM sys.server_permissions perm
JOIN sys.server_principals prin
    ON perm.grantee_principal_id = prin.principal_id
WHERE perm.permission_name = 'VIEW SERVER STATE';

For specific monitoring needs, consider more granular approaches:

-- Server audit specification for login tracking
CREATE SERVER AUDIT [LoginAudit]
TO FILE (FILEPATH = 'C:\Audits\');
GO

CREATE SERVER AUDIT SPECIFICATION [LoginAuditSpec]
FOR SERVER AUDIT [LoginAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP);
GO