Infrastructure at your Service

It’s every time hard to replace a login who as role and permissions in every database like the service account for SCOM.
In the previous version of Scom, you create a service account (dbi\scom) to access the SQL Server and check the health of the databases.
Now it’s possible to use the System Center Operations Manager Health Service (NT SERVICE\HealthService).
To do this change on all SQL Servers, I write a script.

I create the script to replace the old SCOM login with the new one on 5 steps:

  • Step 1: kill sessions with the old scom login
  • Step 2: drop the old user scom in all databases
  • Step 3: drop server role scom in the instance
  • Step 4: Drop the old scom login
  • Step 5: Set the new scom account

Step 1: kill sessions with the old scom login

Before dropping the login, you need to be sure that he has no more connection to the instance.
Most of the time, the service will be stop but this script insure you that no process is connected.

/*Step 1: kill sessions with the old scom login*/
DECLARE @LoginToDrop sysname SET @LoginToDrop='dbi\scom';
DECLARE @SessionToKill nvarchar(max) SET @SessionToKill = ''; 

SELECT @[email protected] + 'Kill '+ CAST(session_id as nvarchar(10)) +';' 
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1 and [email protected];
PRINT (@SessionToKill) 
EXEC(@SessionToKill)

 

Step 2: drop the old user scom in all databases

The second part of the script is to go though all databases and drop the user-databases

/*Step 2: drop user scom in all databases sessions with the old scom session */
DECLARE @DropDatabaseUserAndRole nvarchar(max);
SET @DropDatabaseUserAndRole = ''; 
SELECT @DropDatabaseUserAndRole = @DropDatabaseUserAndRole  + ' USE ' + QUOTENAME(db.name) + '; DROP USER ['[email protected]+'];' 
  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); 
PRINT (@DropDatabaseUserAndRole ) 
EXEC(@DropDatabaseUserAndRole ) 
GO

 

Step 3: drop server role scom in the instance

This step is optional and only if you create a server role for the scom login. In this script, the first part is to drop all members from the role and after drop the role

/*Step 3: drop server role scom in the instance*/
USE [master]
GO
DECLARE @RoleName sysname;
set @RoleName = N'scom';
IF @RoleName <> N'public' and (select is_fixed_role from sys.server_principals where name = @RoleName) = 0
BEGIN
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.server_principals
    where principal_id in (select member_principal_id from sys.server_role_members where role_principal_id in 
           (select principal_id FROM sys.server_principals where [name] = @RoleName  AND type = 'R' ))
    OPEN Member_Cursor;
    FETCH NEXT FROM Member_Cursor into @RoleMemberName
    DECLARE @SQL NVARCHAR(4000)
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = 'ALTER SERVER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')
        EXEC(@SQL)
        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    END;
    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
END
DROP SERVER ROLE [scom]
GO

In this script, i use a cursor, it’s not the best but it’s efficient…

Step 4: Drop the old scom login

After these 3 steps, I can now drop the login scom

/*Step 4: Drop the scom login*/
USE [master]
GO
DROP LOGIN [dbi\scom]
GO

 

Step 5: Set the new scom account

Now, it’ the time to add the new one.
The new service account is the System Center Operations Manager Health Service (NT SERVICE\HealthService).
After Adding the login to the instance, I create the role SCOM_HealthService in each database and add this login as member.
Also give the server permissions “VIEW ANY DATABASE”,”VIEW SERVER STATE” and “VIEW ANY DEFINITION” and specials permissions in the master and msdb databases.

/*Step 5: Set the new scom account*/
USE [master];
SET NOCOUNT ON;
DECLARE @accountname sysname = 'NT SERVICE\HealthService';
-- Create the server role and grant instance level permissions
CREATE SERVER ROLE [SCOM_HealthService];
GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
DECLARE @createLoginCommand nvarchar(200);
SET @createLoginCommand = ' CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'
EXEC(@createLoginCommand);
-- Add the login to the user defined server role 
EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService' 
-- 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) 
-- Add database specific permissions to database role
USE [master]; 
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService]
GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService];
USE [msdb]; 
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].[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]; 
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService';
EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM_HealthService';

 
 

Global Script

To finish my post, I give you also the global script:

/*Step 1: kill sessions with the old scom login*/
DECLARE @LoginToDrop sysname SET @LoginToDrop='dbi\scom';
DECLARE @SessionToKill nvarchar(max) SET @SessionToKill = ''; 

SELECT @[email protected] + 'Kill '+ CAST(session_id as nvarchar(10)) +';' 
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1 and [email protected];
PRINT (@SessionToKill) 
EXEC(@SessionToKill) 

/*Step 2: drop user scom in all databases sessions with the old scom session */
DECLARE @DropDatabaseUserAndRole nvarchar(max);
SET @DropDatabaseUserAndRole = ''; 
SELECT @DropDatabaseUserAndRole = @DropDatabaseUserAndRole  + ' USE ' + QUOTENAME(db.name) + '; DROP USER ['[email protected]+'];' 
  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); 
PRINT (@DropDatabaseUserAndRole ) 
EXEC(@DropDatabaseUserAndRole ) 
GO

/*Step 3: drop server role scom in the instance*/

USE [master]
GO

DECLARE @RoleName sysname
set @RoleName = N'scom'

IF @RoleName <> N'public' and (select is_fixed_role from sys.server_principals where name = @RoleName) = 0
BEGIN
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.server_principals
    where principal_id in ( 
        select member_principal_id 
        from sys.server_role_members 
        where role_principal_id in (
            select principal_id
            FROM sys.server_principals where [name] = @RoleName  AND type = 'R' ))

    OPEN Member_Cursor;

    FETCH NEXT FROM Member_Cursor
    into @RoleMemberName

    DECLARE @SQL NVARCHAR(4000)
        
    WHILE @@FETCH_STATUS = 0
    BEGIN
        
        SET @SQL = 'ALTER SERVER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')
        EXEC(@SQL)
        
        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    END;

    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
END
DROP SERVER ROLE [scom]
GO

/*Step 4: Drop the scom login*/
USE [master]
GO
DROP LOGIN [dbi\scom]
GO


/*Step 5: Set the new scom account*/
USE [master];
SET NOCOUNT ON;
DECLARE @accountname sysname = 'NT SERVICE\HealthService';
-- Create the server role and grant instance level permissions
CREATE SERVER ROLE [SCOM_HealthService];
GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
DECLARE @createLoginCommand nvarchar(200);
SET @createLoginCommand = ' CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'
EXEC(@createLoginCommand);
-- Add the login to the user defined server role 
EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService' 
-- 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) 
-- Add database specific permissions to database role
USE [master]; 
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService]
GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService];
USE [msdb]; 
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].[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]; 
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService';
EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM_HealthService';

 
I hope this script can help you for all migrations of service account present in all databases.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant