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.

Important:

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.

See: Creating a Credential (Microsoft SQL Server)

Required Permissions and Role

To create your MS SQL least-privileged user (LPU), you will need to complete the following general tasks:

  1. Grant your MS SQL login credentials the following permissions:

    • VIEW SERVER STATE

    • VIEWDATABASE STATE

    • VIEW ANY DEFINITION

  2. 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)