This topic contains the minimum MS SQL user permissions needed for an MS SQL Server Management Pack monitoring user. An automated script is provided below to help you create a local LPU user.
It is recommended to create your LPU prior to installing and configuring the Management Pack. You should use your own MS SQL credentials (login), user, and role names.
Required Permissions and Role
To create your MS SQL least-privileged user (LPU), you will need to complete the following general tasks:
Grant your MS SQL login credentials the following permissions:
VIEW SERVER STATE
VIEWDATABASE STATE
VIEW ANY DEFINITION
Next, to ensure all future-created databases are monitored, create a user that maps to your MS SQL credentials and has thepublic role on the following databases:
eachuserdatabase
msdb database
model database
Create Local LPU User Script
Run the following script to automate creation of a local MS SQL Server LPU user with the permissions/role mentioned above:
use master; CREATE LOGIN sql_lpu WITH PASSWORD = 'your-password'; CREATE USER sql_lpu FOR LOGIN sql_lpu; GRANT CONNECT SQL to sql_lpu; GRANT VIEW SERVER STATE to sql_lpu; GRANT VIEW DATABASE STATE TO sql_lpu; GRANT VIEW ANY DEFINITION TO sql_lpu; GO GRANT EXEC on [xp_readerrorlog] to sql_lpu GO use model; CREATE USER sql_lpu FOR LOGIN sql_lpu; GO use msdb; CREATE USER sql_lpu FOR LOGIN sql_lpu; GRANT SELECT ON [dbo].[syssessions] TO sql_lpu GRANT SELECT ON [dbo].[sysjobhistory] to sql_lpu GRANT SELECT ON [dbo].[sysjobsteps] TO sql_lpu GRANT SELECT ON [dbo].[sysjobs_view] TO sql_lpu GRANT SELECT ON [dbo].[sysjobactivity] TO sql_lpu ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [sql_lpu] -- For SQL Server 2008 R2, use: sp_addrolemember [SQLAgentReaderRole], [sql_lpu] -- Goes through each user database and adds public permissions DECLARE @name NVARCHAR(MAX) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE('USE ' + @name + '; CREATE USER sql_lpu FOR LOGIN sql_lpu;' ); FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
Tables, Views, and Functions Used by Permission
Permission: VIEW ANY DATABASE
master.dbo.sysdatabases
sys.databases
Permission: EXEC on [xp_readerrorlog]
master.dbo.xp_readerrorlog
Permission: GRANT SELECT ON [dbo].[sysjobactivity]
msdb.dbo.sysjobactivity
Permission: GRANT SELECT ON [dbo].[sysjobhistory]
msdb.dbo.sysjobhistory
Permission: GRANT SELECT ON [dbo].[sysjobs_view]
msdb.dbo.sysjobs_view
Permission: GRANT SELECT ON [dbo].[sysjobsteps]
msdb.dbo.sysjobsteps
Permission: GRANT VIEW SERVER STATE
sys.all_views
Permission: public
sys.allocation_units
sys.configurations
sys.database_files
sys.partitions
sys.sysusers
Permission: VIEW ANY DEFINITION
sys.availability_groups
sys.availability_replicas
sys.indexes
sys.internal_tables
sys.master_files
sys.objects
sys.syslogins
Permission: VIEW DATABASE STATE
sys.dm_db_index_physical_stats
Permission: VIEW SERVER STATE
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text
sys.dm_hadr_availability_group_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_database_replica_states
sys.dm_io_virtual_file_stats
sys.dm_os_buffer_descriptors
sys.dm_os_cluster_nodes
sys.dm_os_performance_counters
sys.dm_os_process_memory
sys.dm_os_schedulers
sys.dm_os_sys_info
sys.dm_os_sys_memory
sys.dm_os_volume_stats
sys.dm_os_wait_stats
sys.dm_resource_governor_resource_pools
sys.sysprocesses
What to do next
Adding an SSL Certificate to the vROps Truststore (Microsoft SQL Server)