Configure SCOM Health Service account for SQL Server

SQL Server 2014 (12.x) introduced the CONNECT ANY DATABASE permission. With this permission it becomes unnecessary to add the SCOM Health Service account to each individual user database. The script in the Microsoft docs does not make use of the new permissions. I created my own version of the script and made it idempotent.

Executing the script will remove all configuration related to the Health Service account and then reapply the configuration so the result will always be the same. If you used the script from the Microsoft docs, the one from Kevin Holman or even and older version of my script it should execute without issues and apply the latest configuration.

Details of what the script does and why can be found below with the full script at the end of this blog post.

Cleanup current configuration

If any existing Health Service configuration exists we need to remove this configuration before we apply the new configuration. The database user and database role are removed from all databases then the Health Service login and server role are removed from the SQL Server. This gives us a clean environment to start from.

USE [master];

SET NOCOUNT ON;

DECLARE @accountname sysname = 'NT SERVICEHealthService';
DECLARE @rolename sysname = 'SCOM_HealthService';

DECLARE @createLoginCommand NVARCHAR(MAX) = 'CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE = [master];';
DECLARE @createusercommand NVARCHAR(MAX) = 'CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + ' WITH DEFAULT_SCHEMA = [dbo];';

-- Remove current configuration from all databases
DECLARE @droprolecommand NVARCHAR(MAX) = 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE [name] = ''' + @rolename + ''') BEGIN DROP ROLE ' + QUOTENAME(@rolename) + '; END;'
DECLARE @dropschemacommand NVARCHAR(MAX) = 'USE [?]; IF EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = ''' + @accountname + ''') BEGIN DROP SCHEMA ' + QUOTENAME(@accountname) + ' END;';

DECLARE @dropusercommand NVARCHAR(MAX) = 'USE [?]; IF EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = ''' + @accountname + ''') BEGIN DROP USER ' + QUOTENAME(@accountname) + ' END;';

EXECUTE sp_MSforeachdb @command1 = @dropschemacommand, @command2 = @dropusercommand, @command3 = @droprolecommand

-- Remove server level configuration
DECLARE @droplogincommand NVARCHAR(MAX) = 'IF EXISTS (SELECT 1 FROM sys.server_principals WHERE [name] = ''' + @accountname + ''') BEGIN DROP LOGIN ' + QUOTENAME(@accountname) + ' END;';
DECLARE @dropserverrolecommand NVARCHAR(MAX) = 'IF EXISTS (SELECT 1 FROM sys.server_principals WHERE [name] = ''' + @accountname + ''') BEGIN DROP LOGIN ' + QUOTENAME(@accountname) + ' END;';

EXEC (@droplogincommand)
EXEC (@dropserverrolecommand)

Drop the login.

EXEC(@droplogincommand)

Drop the server role on supported version. User defined server roles were added in SQL Server 2012.

IF CAST(SERVERPROPERTY('ProductMajorVersion') AS int) >= 11
BEGIN
  EXEC(@dropserverrolecommand)
END;

Create login and add server level permissions

The first step to apply a new configuration is to add the Health Service account as a login to SQL Server, create the server role with the correct permissions and assign the login to this role. For SQL Server 2014 and higher the server level permissions CONNECT ANY DATABASE is used. For older versions of SQL Server a database user is created in each database. The result is the same but the server level permission is a lot easier to manage.

USE [master];

SET NOCOUNT ON;

DECLARE @accountname sysname = 'NT SERVICEHealthService';

-- Create the server role and grant instance level permissions
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'SCOM_HealthService' AND [type] = 'R')
BEGIN
CREATE SERVER ROLE [SCOM_HealthService];
END;

GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks

-- Add the GRANT CONNECT ANY DATABASE permission to SQL Server 2014 and newer
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS int) >= 12
BEGIN
EXEC ('GRANT CONNECT ANY DATABASE TO [SCOM_HealthService];');
END;

-- Create the login on server level
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @accountname AND [type] = 'U')
BEGIN
  DECLARE @createLoginCommand NVARCHAR(MAX);
  SET @createLoginCommand = ' CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'
  EXEC(@createLoginCommand);
END;

EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService'

-- Create database specific roles and users for SQL Server 2012 and older
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS int) < 12
BEGIN
  -- Add the login and database role to each database
  DECLARE @createDatabaseUserAndRole nvarchar(max) SET @createDatabaseUserAndRole = '';
  SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ' USE ' + QUOTENAME(db.name) + '; CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + '; CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ''SCOM_HealthService'', @membername = '+ QUOTENAME(@accountname) + ''
  FROM sys.databases db
  LEFT JOIN sys.dm_hadr_availability_replica_states hadrstate
    ON db.replica_id = hadrstate.replica_id
  WHERE db.database_id != 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0 AND (hadrstate.role = 1 or hadrstate.role is null);
  EXEC(@createDatabaseUserAndRole)
END;

DECLARE @createusercommand NVARCHAR(MAX) = 'CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + ';';

Add master database configuration

We’re adding some specific permissions in the master database so SCOM will be able to read the SQL Server Logs.

-- Add master database specific permissions
USE [master]

DECLARE @createusercommand NVARCHAR(MAX) = 'CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + ';';

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = 'SCOM_HealthService' AND [type] = 'R')
BEGIN
   EXEC('CREATE ROLE [SCOM_HealthService];');
END;
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService];

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
   EXEC(@createusercommand);
END;
EXEC sp_addrolemember @membername = @accountname, @rolename = 'SCOM_HealthService';

Add msdb database configuration

We’re adding some specific permissions in the msdbdatabase so SCOM will be able to monitor the SQL Server Agent Job health.

-- CONFIGURE MSDB DATABASE SPECIFIC PERMISSIONS
USE [msdb];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = 'SCOM_HealthService' AND [type] = 'R')
BEGIN
    EXEC('CREATE ROLE [SCOM_HealthService];');
END;
 
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
    EXEC(@createusercommand);
END;
 
EXEC sp_addrolemember @membername = @accountname, @rolename = 'SCOM_HealthService';
 
GRANT SELECT on [dbo].[sysjobschedules] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[sysschedules] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[sysjobs_view] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[syscategories] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService];
GRANT EXECUTE on [dbo].[sp_help_job] TO [SCOM_HealthService];
GRANT EXECUTE on [dbo].[sp_help_jobactivity] TO [SCOM_HealthService];
GRANT EXECUTE on [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService];
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername= 'SCOM_HealthService';
EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername= 'SCOM_HealthService';

Full Script

USE [master];
 
SET NOCOUNT ON;
 
DECLARE @accountname sysname = 'NT SERVICEHealthService';
 
-- Create the server role and grant instance level permissions
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'SCOM_HealthService' AND [type] = 'R')
BEGIN
    CREATE SERVER ROLE [SCOM_HealthService];
END;
 
GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks
 
-- Add the GRANT CONNECT ANY DATABASE permission to SQL Server 2014 and newer
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS int) >= 12
BEGIN
    EXEC ('GRANT CONNECT ANY DATABASE TO [SCOM_HealthService];');
END;
 
-- Create the login on server level
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @accountname AND [type] = 'U')
BEGIN
    DECLARE @createLoginCommand NVARCHAR(MAX);
    SET @createLoginCommand = ' CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'
    EXEC(@createLoginCommand);
END;
 
EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService'
 
-- Create database specific roles and users for SQL Server 2012 and older
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS int) < 12
BEGIN
   -- Add the login and database role to each database
    DECLARE @createDatabaseUserAndRole nvarchar(max) SET @createDatabaseUserAndRole = '';
    SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ' USE ' + QUOTENAME(db.name) + ';
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = ''' + @accountname + ''' AND [type] = ''U'')  CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + ';
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = ''SCOM_HealthService'' AND [type] = ''R'') CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ''SCOM_HealthService'', @membername = '+ QUOTENAME(@accountname) + ''
      -- 'ALTER ROLE [SCOM_HealthService] ADD MEMBER ' + QUOTENAME(@accountname) + ';'
    FROM sys.databases db
    LEFT JOIN sys.dm_hadr_availability_replica_states hadrstate
        ON db.replica_id = hadrstate.replica_id
    WHERE db.database_id != 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0 AND (hadrstate.role = 1 or hadrstate.role is null);
    EXEC(@createDatabaseUserAndRole)
END;
 
DECLARE @createusercommand NVARCHAR(MAX) = 'CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + ';';
 
-- CONFIGURE MASTER DATABASE SPECIFIC PERMISSIONS
USE [master];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = 'SCOM_HealthService' AND [type] = 'R')
BEGIN
    EXEC('CREATE ROLE [SCOM_HealthService];')
END;
 
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
    EXEC(@createusercommand);
END;
 
-- Add database specific permissions to database role
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService];
 
EXEC sp_addrolemember @membername = @accountname, @rolename = 'SCOM_HealthService';
 
-- CONFIGURE MSDB DATABASE SPECIFIC PERMISSIONS
USE [msdb];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = 'SCOM_HealthService' AND [type] = 'R')
BEGIN
    EXEC('CREATE ROLE [SCOM_HealthService];');
END;
 
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
    EXEC(@createusercommand);
END;
 
EXEC sp_addrolemember @membername = @accountname, @rolename = 'SCOM_HealthService';
 
GRANT SELECT on [dbo].[sysjobschedules] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[sysschedules] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[sysjobs_view] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[syscategories] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService];
GRANT SELECT on [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService];
GRANT EXECUTE on [dbo].[sp_help_job] TO [SCOM_HealthService];
GRANT EXECUTE on [dbo].[sp_help_jobactivity] TO [SCOM_HealthService];
GRANT EXECUTE on [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService];
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername= 'SCOM_HealthService';
EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername= 'SCOM_HealthService';

Navigeer naar

Wijzig taal

Configure SCOM Health Service account for SQL Server

Cleanup current configuration

Create login and add server level permissions

Add master database configuration

Add msdb database configuration

Full Script

Geïntresseerd in meer blogs?

Datum van publicatie

Leestijd

Auteur

Configure SCOM Health Service account for SQL Server

Cleanup current configuration

Create login and add server level permissions

Add master database configuration

Add msdb database configuration

Full Script

Deel dit artikel

Geïntresseerd in meer blogs?

Datum van publicatie

Leestijd

Auteur

Gerelateerde blogs

Validating Azure Bicep templates with PSRule

Import a GraphQL API in API Management using Powershell

Interpreteerbare AI als oplossing voor het Black Box Probleem: van paper naar demo (deel 2/2)

Interpreteerbare AI als oplossing voor het Black Box Probleem: van paper naar demo (deel 1/2)