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; it can help you create a local LPU user or can be modified for use with a domain 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.

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 VMware Aria Operations Truststore