Configuring SQL Logins for SCOM Accounts Made Easy

Credit where it’s due: tidbytez and K. Brian Kelley on mssqltips.com


There are two scenarios that I’ll cover here:

  1. Clone an existing SQL login with comprehensive stored procedures.
    This is useful when changing/replacing existing SCOM accounts.
  2. Create new logins with appropriate permissions based on login type/role.
    This is useful for:
    Moving/restoring a SCOM database to a new SQL instance where the SCOM logins do not already exist.
    Creating SCOM accounts and permissions on AG availability replicas/partners.
    Correcting SCOM account permissions if they drift from the original config at time of installation.

*Before new SQL logins can be used with SCOM, follow THESE configuration steps.


Scenario 1: Clone an Existing SQL Login

Overview

  1. Run the main script to create 5 stored procedures.
  2. Execute only one of the new stored procedures (#5) to dynamically create the SQL cloning script. This will only display the cloning script text which you will use in the final step (#3), below.
  3. Execute the cloning script to clone an existing SQL login.


1) Run the Main Script

The main script will only create the stored procedures which are later used for cloning a login.

[1] CLONE_CloneLogin
[2] CLONE_CloneDBPerms
[3] CLONE_CreateUserInDB
[4] CLONE_GrantUserRoleMembership
[5] CLONE_CloneLoginAndAllDBPerms

These stored procedures will need to be created on each SQL instance wherever your three SCOM databases may exist: OperationsManager, OperationsManagerDW, ReportServer. Think of a SQL stored procedure as a task that can be used over and over again, similar to a function in a programming language. The stored procedures can be created on any existing database. If your OperationsManager and OperationsManagerDW databases are on separate SQL instances, then create the stored procedures once on each SQL instance. The same goes for the Reporting databases; if they exist on a separate instance, the stored procedures will need to exist on a database on that instance. If Reporting and DW are on the same SQL instance, then you need the stored procedures on only one database (I suggest the DW).

These stored procedures will need to be created on a single DB for each SQL instance wherever your three SCOM databases may exist.

I’m going to spell this out as clear as possible.

This script below will need to be customized with the name of an appropriate database. If you have an “all in one” SQL server where all three of your core SCOM databases exist (Ops, DW, Reporting) then I suggest you add the stored procedures to “OperationsManager”; Find/Replace the string “MY_DATABASE_NAME” with “OperationsManager”.
The point is, on whichever SQL instance you run this Main script (to create the 5 stored procedures) you must replace the generic value ‘MY_DATABASE_NAME’ with the name of an appropriate database. There are 9 places in the Main script where this DB name must be updated. Use CTRL+H (in SQL Server Management Studio query editor) to find and replace the text.

This is the “Main Script”

/* This is the "MAIN SCRIPT"
THERE ARE 5 SPs AS PART OF THIS PROCESS, ONE BEING THE MASTER SP TO CALL THE OTHER FOUR

[1] CLONE_CloneLogin
[2] CLONE_CloneDBPerms
[3] CLONE_CreateUserInDB
[4] CLONE_GrantUserRoleMembership

[5] CLONE_CloneLoginAndAllDBPerms

*/
USE [MY_DATABASE_NAME];
GO

IF OBJECT_ID('dbo.CLONE_CloneLogin') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CLONE_CloneLogin] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[CLONE_CloneLogin] @NewLogin SYSNAME
	,@NewLoginPwd NVARCHAR(MAX)
	,@WindowsLogin CHAR(1)
	,@LoginToClone SYSNAME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);

	CREATE TABLE #CLONE_CloneLoginScript (SqlCommand NVARCHAR(MAX));

	SET @SQL = '/' + '*' + 'BEGIN: CLONE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT @SQL;

	SET @SQL = '/' + '*' + 'CREATE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT @SQL;

	IF (@WindowsLogin = 'T')
	BEGIN
		SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] FROM WINDOWS;'

		INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
		SELECT @SQL
	END
	ELSE
	BEGIN
		SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] WITH PASSWORD = N''' + @NewLoginPwd + ''';';

		INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'CLONE SERVER ROLES' + '*' + '/';

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT @SQL

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + @NewLogin + ''', @rolename = ''' + R.NAME + ''';' AS 'SQL'
	FROM sys.server_role_members AS RM
	JOIN sys.server_principals AS L ON RM.member_principal_id = L.principal_id
	JOIN sys.server_principals AS R ON RM.role_principal_id = R.principal_id
	WHERE L.NAME = @LoginToClone;

	IF @@ROWCOUNT = 0
	BEGIN
		SET @SQL = '/' + '*' + '---- No Server Roles To Clone' + '*' + '/';;

		INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'CLONE SERVER PERMISSIONS' + '*' + '/';

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT @SQL;

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT [SQL]
	FROM (
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' TO [' + @NewLogin + '] WITH GRANT OPTION;'
				ELSE 'USE master;  ' + P.state_desc + ' ' + P.permission_name + ' TO [' + @NewLogin + '];'
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 100
			AND P.type <> 'COSQ'
		
		UNION ALL
		
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' ON LOGIN::[' + L2.NAME + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
				ELSE 'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON LOGIN::[' + L2.NAME + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		JOIN sys.server_principals AS L2 ON P.major_id = L2.principal_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 101
		
		UNION ALL
		
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' ON ENDPOINT::[' + E.NAME + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
				ELSE 'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON ENDPOINT::[' + E.NAME + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		JOIN sys.endpoints AS E ON P.major_id = E.endpoint_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 105
		) AS ServerPermission;

	IF @@ROWCOUNT = 0
	BEGIN
		SET @SQL = '/' + '*' + '---- No Server Permissions To Clone' + '*' + '/';;

		INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'END: CLONE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CLONE_CloneLoginScript (SqlCommand)
	SELECT @SQL;

	SELECT *
	FROM #CLONE_CloneLoginScript
END;
GO

USE [MY_DATABASE_NAME];
GO

IF OBJECT_ID('dbo.CLONE_CreateUserInDB') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CLONE_CreateUserInDB] AS SELECT 1')
GO

ALTER PROC [dbo].[CLONE_CreateUserInDB] @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneDbUserScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CREATE DATABASE USER' + '*' + '/';

	INSERT INTO #CloneDbUserScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = '
INSERT INTO #CloneDbUserScript (SqlCommand)
SELECT ''USE [' + @DbName + ']; 
IF EXISTS(SELECT name FROM sys.database_principals 
WHERE name = ' + '''''' + @LoginToClone + '''''' + ')
BEGIN
CREATE USER [' + @NewLogin + '] FROM LOGIN [' + @NewLogin + '];
END;''';

		EXEC (@SQL);
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneDbUserScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database User To Create' + '*' + '/';;

		INSERT INTO #CloneDbUserScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CREATE DATABASE USER' + '*' + '/';

	INSERT INTO #CloneDbUserScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneDbUserScript;

	DROP TABLE #CloneDbUserScript;
END;
GO

USE [MY_DATABASE_NAME];
GO

IF OBJECT_ID('dbo.CLONE_GrantUserRoleMembership') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CLONE_GrantUserRoleMembership] AS SELECT 1')
GO

ALTER PROC dbo.CLONE_GrantUserRoleMembership @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneRoleMembershipScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CLONE DATABASE ROLE MEMBERSHIP' + '*' + '/';

	INSERT INTO #CloneRoleMembershipScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = '
INSERT INTO #CloneRoleMembershipScript (SqlCommand) 
SELECT ''USE [' + @DBName + ']; EXEC sp_addrolemember @rolename = '''''' + r.name 
+ '''''', @membername = ''''' + @NewLogin + ''''';''
FROM [' + @DBName + '].sys.database_principals AS U
JOIN [' + @DBName + '].sys.database_role_members AS RM
ON U.principal_id = RM.member_principal_id
JOIN [' + @DBName + '].sys.database_principals AS R
ON RM.role_principal_id = R.principal_id
WHERE U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL);
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneRoleMembershipScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database Roles To Clone' + '*' + '/';;

		INSERT INTO #CloneRoleMembershipScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CLONE DATABASE ROLE MEMBERSHIP' + '*' + '/';

	INSERT INTO #CloneRoleMembershipScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneRoleMembershipScript;

	DROP TABLE #CloneRoleMembershipScript;
END;
GO

USE [MY_DATABASE_NAME];
GO

IF OBJECT_ID('dbo.CLONE_CloneDBPerms') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CLONE_CloneDBPerms] AS SELECT 1')
GO

ALTER PROC dbo.CLONE_CloneDBPerms @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(max);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneDbPermissionScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CLONE DATABASE PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneDbPermissionScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON DATABASE::[' + @DbName + '] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON DATABASE::[' + @DbName + '] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	WHERE class = 0
	  AND P.[type] <> ''CO''
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = P.major_id
	WHERE class = 3
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.objects AS O
		ON O.object_id = P.major_id
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = O.schema_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id = 0';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '] WITH GRANT OPTION;'' 
		 COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '];'' 
		 COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.objects AS O
		ON O.object_id = P.major_id
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = O.schema_id
	  JOIN [' + @DbName + '].sys.columns AS C
		ON C.column_id = P.minor_id AND o.object_id = C.object_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + 
			'''
	  AND P.major_id > 0
	  AND P.minor_id > 0;'

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.database_principals AS U2
		ON U2.principal_id = P.major_id
	WHERE class = 4
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.symmetric_keys AS K
		ON P.major_id = K.symmetric_key_id
	WHERE class = 24
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.asymmetric_keys AS K
		ON P.major_id = K.asymmetric_key_id
	WHERE class = 26
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.certificates AS C
		ON P.major_id = C.certificate_id
	WHERE class = 25
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneDbPermissionScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database Permissions To Clone' + '*' + '/';;

		INSERT INTO #CloneDbPermissionScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CLONE DATABASE PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneDbPermissionScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneDbPermissionScript;

	DROP TABLE #CloneDbPermissionScript;
END;
GO

USE [MY_DATABASE_NAME];
GO

IF OBJECT_ID('dbo.CLONE_CloneLoginAndAllDBPerms') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CLONE_CloneLoginAndAllDBPerms] AS SELECT 1')
GO

ALTER PROC dbo.CLONE_CloneLoginAndAllDBPerms @NewLogin SYSNAME
	,@NewLoginPwd NVARCHAR(MAX)
	,@WindowsLogin CHAR(1)
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #CLONE_CloneLoginAndAllDBPermsScript (SqlCommand NVARCHAR(MAX));

	INSERT INTO #CLONE_CloneLoginAndAllDBPermsScript
	EXEC MY_DATABASE_NAME.dbo.CLONE_CloneLogin @NewLogin = @NewLogin
		,@NewLoginPwd = @NewLoginPwd
		,@WindowsLogin = @WindowsLogin
		,@LoginToClone = @LoginToClone;

	INSERT INTO #CLONE_CloneLoginAndAllDBPermsScript
	EXEC MY_DATABASE_NAME.dbo.CLONE_CreateUserInDB @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	INSERT INTO #CLONE_CloneLoginAndAllDBPermsScript
	EXEC MY_DATABASE_NAME.dbo.CLONE_GrantUserRoleMembership @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	INSERT INTO #CLONE_CloneLoginAndAllDBPermsScript
	EXEC MY_DATABASE_NAME.dbo.CLONE_CloneDBPerms @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	SELECT SqlCommand
	FROM #CLONE_CloneLoginAndAllDBPermsScript

	DROP TABLE #CLONE_CloneLoginAndAllDBPermsScript
END;
GO


Execute the Main Script

When you execute the Main script, it will create 5 stored procedures on the target database. Use SQL Server Management Studio.

Example:


2) Execute Stored Procedure #5 to Generate SQL Script Text


NOTE: This step will only output text to the screen and will change nothing.

In this step we will execute only a single stored procedure (CLONE_CloneLoginAndAllDBPerms) with a few parameters.

There are only two variables that you need to worry about:
@NewLogin – This is the new account name
@LoginToClone – This is the existing SQL login that you will clone

Note:
@NewLoginPwd – used only if account is SQL account and not Windows Active Directory account. This is not relevant for SCOM.
@WindowsLogin – This should always be ‘T’ for True (for SCOM).
@DatabaseName – This will scope the generated script to only the specified database. Leave NULL to reveal all applicable permissions related to the existing account.

The new clone account must already exist in Active Directory. It is assumed that you have used separate accounts (best practice) for the various SCOM roles:

OMAA – Management Server Action Account
DAS/SDK – Data Access Service and SCOM
DW Reader – Data Reader account is used to deploy reports.
DW Writer – Data Warehouse Write account

EXEC [dbo].[CLONE_CloneLoginAndAllDBPerms] @NewLogin = N'YOURDOMAIN\NEWACCOUNT'
	,@NewLoginPwd = NULL
	,@WindowsLogin = N'T'
	,@LoginToClone = N'YOURDOMAIN\existingACCOUNT'
	,@DatabaseName = NULL

Copy/paste this snippet into your query editor.
Customize this snippet with your account names.
Run the query.

The script text is dynamically generated for YOU, for YOUR SQL login, for YOUR SQL instance. No SQL logins have been created or modified at this point. Again, this step will only output “Clone” script text to the screen.


3) Execute the Clone Script

Now that the Clone script has been created (output to the screen), you must execute it to create the new SQL login and DB user (clone) for the new domain account.

Copy/past your customized Clone script from the SQL query output window into a new SQL query editor window. Example of my customized Clone script shown in the screenshot above.

Here is my example Clone script for the DAS/SDK login where the SCOM 2019 OperationsManager database exists alone on the instance (separate from the DW and Report DBs).

(SCOM 2019)

After running my cusomized Clone script, you can see the new login has been created. The new login and old login have identical permissions on the relevant databases for this SCOM 2019 example.
[msdb]

[OperationsManager]

I had to modify this screenshot to show the full list of permissions for each login.

The Clone script must be generated and executed once for each account on each SQL instance where it is relevant.

Relevant DatabaseAccount/Login
OperationsManagerDAS/SDK
OMAA
DWWrite*
OperationsManagerDWDAS/SDK
DWRead
DWWrite*
ReportServerDWRead

*Before you can use the new/cloned DWWrite account, you must set the new account name in this table/view: OperationsManagerDW.dbo.vManagementGroup
See instructions HERE


In summary, you create a cloning tool (stored procedure) once on each SQL instance. Pick any DB on which to create the SPs. It doesn’t matter much which DB you choose. Then, you clone each existing SCOM account/login by using the customized stored procedure #5.



Scenario 2: Create New SCOM Logins with Appropriate Permissions Based on Login Type/Role.

SCOM 2016/2019


This is useful for:

  • Moving/restoring a SCOM database to a new SQL instance where the SCOM logins do not already exist.
  • Creating SCOM accounts and permissions on AG availability replicas/partners.
  • Correcting SCOM account permissions if they drift from the original config at install. (Will add correct permissions. Will not remove incorrect permissions. However, you could delete the original SQL Login and DB User, then recreate them with this script. )


Note:
a) This script, as is, is appropriate for SCOM2016.
For SCOM2019, UNcomment the ‘2019’ code section under ‘OperationsManager’ .
b) SysAdmin SQL permissions are required for this operation.
c) Your new accounts must already exist in Active Directory.

IMPORTANT! FOLLOW THESE STEPS
1) Perform a FIND/REPLACE (CTRL+H) for the generic account names below with your domain accounts.

YOURDOMAIN\svcOMDAS
YOURDOMAIN\svcOMAA
YOURDOMAIN\svcOMDWWrite
YOURDOMAIN\svcOMDWRead

2) Execute the appropriate sections below wherever the named SCOM database is present on the SQL instance. The result should be a default, “new install” configuration for the standard SCOM accounts.

/*
CreateSCOMAccounts.SQL

Version History:
2021.01.12.1603 - v1

This is useful for:
a) Moving/restoring a SCOM database to a new SQL instance where the SCOM logins do not already exist.
b) Creating SCOM accounts and permissions on AG availability replicas/partners
c) Correcting SCOM account permissions if they drift from the original config at install.


Note: 
a) This script, as is, is appropriate for SCOM2016.  For SCOM2019, UNcomment the section under 'OperationsManager' for SCOM2019.
b) SysAdmin/SA permissions are required for this operation.
c) Your new accounts must already exist in Active Directory.

IMPORTANT! FOLLOW THESE STEPS
1) Perform a FIND/REPLACE (CTRL+H) for the account names below with your domain accounts.
YOURDOMAIN\svcOMDAS
YOURDOMAIN\svcOMAA
YOURDOMAIN\svcOMDWWrite
YOURDOMAIN\svcOMDWRead

2) Execute the appropriate sections below wherever the named SCOM database is present on the SQL instance.

*/
/* ------------------------------------------- */
/* ------------ OperationsManager ------------ */
/* ------------------------------------------- */
IF EXISTS( SELECT name FROM master.sys.databases WHERE name = 'OperationsManager' ) 
	BEGIN
		/*CREATE SERVER LOGIN*/
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMDAS')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMDAS] FROM WINDOWS; END;
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMAA')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMAA] FROM WINDOWS; END;
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMDWWrite')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMDWWrite] FROM WINDOWS; END;

		USE [OperationsManager];
		/*BEGIN: CREATE DATABASE USER*/
		CREATE USER [YOURDOMAIN\svcOMDAS] FROM LOGIN [YOURDOMAIN\svcOMDAS];
		CREATE USER [YOURDOMAIN\svcOMAA] FROM LOGIN [YOURDOMAIN\svcOMAA];
		CREATE USER [YOURDOMAIN\svcOMDWWrite] FROM LOGIN [YOURDOMAIN\svcOMDWWrite];
		/*BEGIN: CLONE DATABASE ROLE MEMBERSHIP*/
		EXEC sp_addrolemember @rolename = 'sdk_users', @membername = 'YOURDOMAIN\svcOMDAS';
		EXEC sp_addrolemember @rolename = 'ConfigService', @membername = 'YOURDOMAIN\svcOMDAS';	
		EXEC sp_addrolemember @rolename = 'dbmodule_users', @membername = 'YOURDOMAIN\svcOMAA';
		EXEC sp_addrolemember @rolename = 'dwsynch_users', @membername = 'YOURDOMAIN\svcOMDWWrite';

		----- SCOM 2019 ONLY  -----
		/*
			USE [msdb]; 
			CREATE USER [YOURDOMAIN\svcOMDAS] FROM LOGIN [YOURDOMAIN\svcOMDAS];
			CREATE USER [YOURDOMAIN\svcOMAA] FROM LOGIN [YOURDOMAIN\svcOMAA];
			EXEC sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = 'YOURDOMAIN\svcOMDAS';
			EXEC sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = 'YOURDOMAIN\svcOMAA';
		*/
		----- SCOM 2019 ONLY  ----- 
	END;


/* --------------------------------------------- */
/* ------------ OperationsManagerDW ------------ */
/* --------------------------------------------- */
IF EXISTS( SELECT name FROM master.sys.databases WHERE name = 'OperationsManagerDW' ) 
	BEGIN
		/*CREATE SERVER LOGIN*/
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMDAS')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMDAS] FROM WINDOWS; END;
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMDWWrite')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMDWWrite] FROM WINDOWS; END;
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMDWRead')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMDWRead] FROM WINDOWS; END;

		USE [OperationsManagerDW];
		/*BEGIN: CREATE DATABASE USER*/
		CREATE USER [YOURDOMAIN\svcOMDAS] FROM LOGIN [YOURDOMAIN\svcOMDAS];
		CREATE USER [YOURDOMAIN\svcOMDWWrite] FROM LOGIN [YOURDOMAIN\svcOMDWWrite];
		CREATE USER [YOURDOMAIN\svcOMDWRead] FROM LOGIN [YOURDOMAIN\svcOMDWRead];

		/*BEGIN: CLONE DATABASE ROLE MEMBERSHIP*/
		EXEC sp_addrolemember @rolename = 'OpsMgrReader', @membername = 'YOURDOMAIN\svcOMDAS';
		EXEC sp_addrolemember @rolename = 'OpsMgrWriter', @membername = 'YOURDOMAIN\svcOMDWWrite';
		EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'YOURDOMAIN\svcOMDWWrite';
		EXEC sp_addrolemember @rolename = 'OpsMgrReader', @membername = 'YOURDOMAIN\svcOMDWRead';
	END;


/*----------------------------------------*/
/* ------------ ReportServer ------------ */
/*----------------------------------------*/
IF EXISTS( SELECT name FROM master.sys.databases WHERE name = 'ReportServer' ) 
	BEGIN
		/*CREATE SERVER LOGIN*/
		IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'YOURDOMAIN\svcOMDWRead')  BEGIN CREATE LOGIN [YOURDOMAIN\svcOMDWRead] FROM WINDOWS; END;

		/*BEGIN: CREATE DATABASE USER and CLONE DATABASE ROLE MEMBERSHIP*/
		USE [ReportServer];
		CREATE USER [YOURDOMAIN\svcOMDWRead] FROM LOGIN [YOURDOMAIN\svcOMDWRead];
		EXEC sp_addrolemember @rolename = 'RSExecRole', @membername = 'YOURDOMAIN\svcOMDWRead';

		USE [ReportServerTempDB];
		CREATE USER [YOURDOMAIN\svcOMDWRead] FROM LOGIN [YOURDOMAIN\svcOMDWRead];
		EXEC sp_addrolemember @rolename = 'RSExecRole', @membername = 'YOURDOMAIN\svcOMDWRead';

		USE [master];
		CREATE USER [YOURDOMAIN\svcOMDWRead] FROM LOGIN [YOURDOMAIN\svcOMDWRead];
		EXEC sp_addrolemember @rolename = 'RSExecRole', @membername = 'YOURDOMAIN\svcOMDWRead';

		USE [msdb];
		CREATE USER [YOURDOMAIN\svcOMDWRead] FROM LOGIN [YOURDOMAIN\svcOMDWRead];
		EXEC sp_addrolemember @rolename = 'RSExecRole', @membername = 'YOURDOMAIN\svcOMDWRead';
	END;



Troubleshooting

This section is likely to grow as users provide feedback and questions.

What if I installed SCOM with a single account for all roles?

I see this from time to time in production environments and it is unfortunate. Your overall experience with the product will be far better (less painful) if you adhere to the best practice of using separate accounts but if you are in this situation, what’s done is done.

Good news! THIS is your opportunity to correct the situation. Use the 2nd scenario/approach to add the separate SCOM logins (OMAA, DAS, DWRead, DWWrite).

8 Replies to “Configuring SQL Logins for SCOM Accounts Made Easy”

  1. Morning, Not a SQL guy. I ran the main script. Now I am stuck on how to “Execute Stored Procedure #5 to Generate SQL Script Text” so that it can do what I need it to?
    Any assistance is greatly appreciated.
    Tony

    1. @Tony,
      I made a few small edits to the article, mostly related to formatting. Please have another look. I tried to make the instructions as clear as possible.

  2. @ Tyson Paul
    Afternoon,
    Thank you! I finally figured it out!
    I ran the main script, then ran this and its output for each of the accounts to clone.
    EXEC [dbo].[CLONE_CloneLoginAndAllDBPerms] @NewLogin = N’YOURDOMAIN\NEWACCOUNT’
    ,@NewLoginPwd = NULL
    ,@WindowsLogin = N’T’
    ,@LoginToClone = N’YOURDOMAIN\existingACCOUNT’
    ,@DatabaseName = NULL
    One thing that is confusing to me about all of this. I read numerous articles where folks are saying to “create the gMSA account with the $ at the end? That is not correct, right?
    Regards,
    Tony

    1. @ Tony,
      The SQL queries don’t create accounts, rather, they create SQL logins for existing accounts. The accounts must first exist in AD. When you initially create the accounts in AD, they automatically get dollar signs (if I recall correctly) due to the gMSA type. Also, IIRC, when you put your account name into the SQL query, it should contain the dollar sign.

      1. Hello Tyson, I cannot believe I put this off for so long and thanks for the awesome document.
        I am in the planning and testing phase of moving from SCOM 2019 UR5 to SCOM 2022 UR1.
        I want to replace the current AD svc accounts now in use with gMSA’s for:
        YOURDOMAIN\svcOMDAS
        YOURDOMAIN\svcOMAA
        YOURDOMAIN\svcOMDWWrite
        YOURDOMAIN\svcOMDWRead
        Reading the microsoft learning stuff, it shows using the Powershell modules to create gMSA accounts without the $.
        Is there a definitive source I can look at that tells me if I need to create the gMSA accounts with or without the $ for use in SCOM 2019 UR5 and SCOM 2022 UR1?
        I want to use the documents here to clone existing AD service accounts and rights over to the new gMSA’s I create in AD.
        Thanks again!
        Tony

        1. @Anthony,
          The PowerShell cmdlet used for creating such an account (gMSA) in Active Directory, “New-ADServiceAccount” (documented here: https://learn.microsoft.com/en-us/powershell/module/activedirectory/new-adserviceaccount?view=windowsserver2022-ps) is specifically for the creation of this account type and there is no need to include the ‘$’.
          See my PowerShell examples here: https://monitoringguys.com/2022/10/06/scom-2022-installation-gmsa-standalone-web-console-server/

Leave a Reply

Your email address will not be published. Required fields are marked *