Getting read-only access to DMV's for querying system and performance stats

Most of what we're going to want to look at when you're having production issues are available through DMV's.

  

If granting select to sys.dm* is a bit too much, these are the tables I'm generally most interested in:

Required Permissions:

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

For Query tuning:

sys.dm_exec_query_stats

sys.dm_exec_sql_text

sys.dm_exec_text_query_plan

For general Waits and locks:

sys.dm_tran_locks

sys.dm_os_wait_stats

For Missing Indexes:

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_details

sys.databases

For IO_STALL:

sys.dm_io_virtual_file_stats

sys.master_files

/* NOTE: The following should not be needed as users are already set up

USE [master]

GO

CREATE USER [theUser] FOR LOGIN [theUser] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [master]

GO

GRANT CONNECT TO [theUser] 

GO

*/

USE [master]

GO

GRANT SELECT ON sys.dm_os_hosts TO [theUser]

GRANT SELECT ON sys.dm_os_memory_brokers TO [theUser]

GRANT SELECT ON sys.dm_os_memory_allocations TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_past_actions TO [theUser]

GRANT SELECT ON sys.dm_xe_session_object_columns TO [theUser]

GRANT SELECT ON sys.dm_os_loaded_modules TO [theUser]

GRANT SELECT ON sys.dm_db_task_space_usage TO [theUser]

GRANT SELECT ON sys.dm_os_memory_objects TO [theUser]

GRANT SELECT ON sys.dm_audit_class_type_map TO [theUser]

GRANT SELECT ON sys.dm_os_schedulers TO [theUser]

GRANT SELECT ON sys.dm_os_server_diagnostics_log_configurations TO [theUser]

GRANT SELECT ON sys.dm_hadr_instance_node_map TO [theUser]

GRANT SELECT ON sys.dm_os_dispatcher_pools TO [theUser]

GRANT SELECT ON sys.dm_os_threads TO [theUser]

GRANT SELECT ON sys.dm_exec_requests TO [theUser]

GRANT SELECT ON sys.dm_tran_commit_table TO [theUser]

GRANT SELECT ON sys.dm_fts_outstanding_batches TO [theUser]

GRANT SELECT ON sys.dm_repl_tranhash TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster TO [theUser]

GRANT SELECT ON sys.dm_qn_subscriptions TO [theUser]

GRANT SELECT ON sys.dm_db_session_space_usage TO [theUser]

GRANT SELECT ON sys.dm_exec_query_optimizer_info TO [theUser]

GRANT SELECT ON sys.dm_xe_map_values TO [theUser]

GRANT SELECT ON sys.dm_tran_top_version_generators TO [theUser]

GRANT SELECT ON sys.dm_fts_fdhosts TO [theUser]

GRANT SELECT ON sys.dm_xe_sessions TO [theUser]

GRANT SELECT ON sys.dm_db_log_space_usage TO [theUser]

GRANT SELECT ON sys.dm_hadr_name_id_map TO [theUser]

GRANT SELECT ON sys.dm_os_waiting_tasks TO [theUser]

GRANT SELECT ON sys.dm_exec_background_job_queue TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_details TO [theUser]

GRANT SELECT ON sys.dm_clr_properties TO [theUser]

GRANT SELECT ON sys.dm_os_sublatches TO [theUser]

GRANT SELECT ON sys.dm_exec_query_memory_grants TO [theUser]

GRANT SELECT ON sys.dm_logpool_hashentries TO [theUser]

GRANT SELECT ON sys.dm_tran_current_snapshot TO [theUser]

GRANT SELECT ON sys.dm_os_wait_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_node_access_stats TO [theUser]

GRANT SELECT ON sys.dm_os_spinlock_stats TO [theUser]

GRANT SELECT ON sys.dm_database_encryption_keys TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_states TO [theUser]

GRANT SELECT ON sys.dm_broker_connections TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_auto_page_repair TO [theUser]

GRANT SELECT ON sys.dm_server_registry TO [theUser]

GRANT SELECT ON sys.dm_os_dispatchers TO [theUser]

GRANT SELECT ON sys.dm_os_stacks TO [theUser]

GRANT SELECT ON sys.dm_filestream_non_transacted_handles TO [theUser]

GRANT SELECT ON sys.dm_xe_session_targets TO [theUser]

GRANT SELECT ON sys.dm_audit_actions TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_group_states TO [theUser]

GRANT SELECT ON sys.dm_os_ring_buffers TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_groups TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster_members TO [theUser]

GRANT SELECT ON sys.dm_db_uncontained_entities TO [theUser]

GRANT SELECT ON sys.dm_exec_cached_plans TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_cluster_states TO [theUser]

GRANT SELECT ON sys.dm_exec_sessions TO [theUser]

GRANT SELECT ON sys.dm_broker_forwarded_messages TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_resource_pools TO [theUser]

GRANT SELECT ON sys.dm_os_memory_clerks TO [theUser]

GRANT SELECT ON sys.dm_hadr_auto_page_repair TO [theUser]

GRANT SELECT ON sys.dm_repl_articles TO [theUser]

GRANT SELECT ON sys.dm_xe_session_events TO [theUser]

GRANT SELECT ON sys.dm_fts_memory_buffers TO [theUser]

GRANT SELECT ON sys.dm_fts_index_population TO [theUser]

GRANT SELECT ON sys.dm_tran_current_transaction TO [theUser]

GRANT SELECT ON sys.dm_os_cluster_properties TO [theUser]

GRANT SELECT ON sys.dm_os_child_instances TO [theUser]

GRANT SELECT ON sys.dm_exec_connections TO [theUser]

GRANT SELECT ON sys.dm_server_memory_dumps TO [theUser]

GRANT SELECT ON sys.dm_exec_background_job_queue_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_broker_clerks TO [theUser]

GRANT SELECT ON sys.dm_filestream_file_io_handles TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_cluster_nodes TO [theUser]

GRANT SELECT ON sys.dm_fts_active_catalogs TO [theUser]

GRANT SELECT ON sys.dm_tran_database_transactions TO [theUser]

GRANT SELECT ON sys.dm_filestream_file_io_requests TO [theUser]

GRANT SELECT ON sys.dm_cdc_log_scan_sessions TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_clock_hands TO [theUser]

GRANT SELECT ON sys.dm_repl_schemas TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_connections TO [theUser]

GRANT SELECT ON sys.dm_db_partition_stats TO [theUser]

GRANT SELECT ON sys.dm_os_sys_memory TO [theUser]

GRANT SELECT ON sys.dm_io_pending_io_requests TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster_networks TO [theUser]

GRANT SELECT ON sys.dm_os_nodes TO [theUser]

GRANT SELECT ON sys.dm_tcp_listener_states TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_entries TO [theUser]

GRANT SELECT ON sys.dm_os_virtual_address_dump TO [theUser]

GRANT SELECT ON sys.dm_cryptographic_provider_properties TO [theUser]

GRANT SELECT ON sys.dm_tran_transactions_snapshot TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_hash_tables TO [theUser]

GRANT SELECT ON sys.dm_cdc_errors TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_configuration TO [theUser]

GRANT SELECT ON sys.dm_exec_query_stats TO [theUser]

GRANT SELECT ON sys.dm_fts_semantic_similarity_population TO [theUser]

GRANT SELECT ON sys.dm_clr_tasks TO [theUser]

GRANT SELECT ON sys.dm_os_worker_local_storage TO [theUser]

GRANT SELECT ON sys.dm_db_persisted_sku_features TO [theUser]

GRANT SELECT ON sys.dm_db_index_usage_stats TO [theUser]

GRANT SELECT ON sys.dm_os_buffer_descriptors TO [theUser]

GRANT SELECT ON sys.dm_tran_active_snapshot_database_transactions TO [theUser]

GRANT SELECT ON sys.dm_server_services TO [theUser]

GRANT SELECT ON sys.dm_tran_active_transactions TO [theUser]

GRANT SELECT ON sys.dm_db_file_space_usage TO [theUser]

GRANT SELECT ON sys.dm_broker_activated_tasks TO [theUser]

GRANT SELECT ON sys.dm_broker_queue_monitors TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_counters TO [theUser]

GRANT SELECT ON sys.dm_tran_session_transactions TO [theUser]

GRANT SELECT ON sys.dm_clr_appdomains TO [theUser]

GRANT SELECT ON sys.dm_exec_trigger_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_pools TO [theUser]

GRANT SELECT ON sys.dm_os_latch_stats TO [theUser]

GRANT SELECT ON sys.dm_io_backup_tapes TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_workload_groups TO [theUser]

GRANT SELECT ON sys.dm_hadr_database_replica_states TO [theUser]

GRANT SELECT ON sys.dm_fts_memory_pools TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_resource_pool_affinity TO [theUser]

GRANT SELECT ON sys.dm_os_sys_info TO [theUser]

GRANT SELECT ON sys.dm_tran_locks TO [theUser]

GRANT SELECT ON sys.dm_exec_procedure_stats TO [theUser]

GRANT SELECT ON sys.dm_hadr_database_replica_cluster_states TO [theUser]

GRANT SELECT ON sys.dm_exec_query_transformation_stats TO [theUser]

GRANT SELECT ON sys.dm_exec_query_resource_semaphores TO [theUser]

GRANT SELECT ON sys.dm_repl_traninfo TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_group_stats TO [theUser]

GRANT SELECT ON sys.dm_fts_population_ranges TO [theUser]

GRANT SELECT ON sys.dm_os_performance_counters TO [theUser]

GRANT SELECT ON sys.dm_os_workers TO [theUser]

GRANT SELECT ON sys.dm_xe_session_event_actions TO [theUser]

GRANT SELECT ON sys.dm_db_script_level TO [theUser]

GRANT SELECT ON sys.dm_server_audit_status TO [theUser]

GRANT SELECT ON sys.dm_io_cluster_shared_drives TO [theUser]

GRANT SELECT ON sys.dm_os_tasks TO [theUser]

GRANT SELECT ON sys.dm_db_fts_index_physical_stats TO [theUser]

GRANT SELECT ON sys.dm_xe_packages TO [theUser]

GRANT SELECT ON sys.dm_logpool_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_nodes TO [theUser]

GRANT SELECT ON sys.dm_tran_version_store TO [theUser]

GRANT SELECT ON sys.dm_os_windows_info TO [theUser]

GRANT SELECT ON sys.dm_os_cluster_nodes TO [theUser]

GRANT SELECT ON sys.dm_os_process_memory TO [theUser]

GRANT SELECT ON sys.dm_xe_objects TO [theUser]

GRANT SELECT ON sys.dm_xe_object_columns TO [theUser]

GRANT SELECT ON sys.dm_clr_loaded_assemblies TO [theUser]

GO

GRANT VIEW SERVER STATE TO [theUser]

GO

GRANT VIEW DATABASE STATE TO [theUser]

GO