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.


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 to dynamically create the SQL cloning script. This will only display the cloning script text.
  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 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:


Execute Stored Procedure #5 to Generate SQL Script Text


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

Execute SP #5 with the appropriate account names for your environment.
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

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

The new clone account must already exist in Active Directory.

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.

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

Customize this snippet with your accounts.

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.


Execute the Clone Script

Now that you have the script, you must execute it to create the new SQL login and DB user (clone) for the new domain account.

Here is my example script for the DAS/SDK login where the SCOM 2019 OperationsManager database exists alone. I copy/pasted the script from the SQL query output window shown in the screenshot above.

(SCOM 2019)

New login is created. 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 stored procedure #5.



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

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

Leave a Reply

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