Credit where it’s due: tidbytez and K. Brian Kelley on mssqltips.com
There are two scenarios that I’ll cover here:
- Clone an existing SQL login with comprehensive stored procedures.
This is useful when changing/replacing existing SCOM accounts. - 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
- Run the main script to create 5 stored procedures.
- 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.
- 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).

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]

The Clone script must be generated and executed once for each account on each SQL instance where it is relevant.
Relevant Database | Account/Login |
OperationsManager | DAS/SDK OMAA DWWrite* |
OperationsManagerDW | DAS/SDK DWRead DWWrite* |
ReportServer | DWRead |
*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”
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
@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.
@ 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
@ 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.
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
@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/