SCOM – T-SQL script to automatize the SQL Server MP Low-Privilege Environment

Hi folks,

If you have the SQL Server Default Action Account and the SQL Server Low Privileges Group defined as the domain users YOURDOMAIN\SCOM-RA-SQL-Action and YOURDOMAIN\SCOM-SQLMPLowPriv this T-SQL script can be usefull for you. Simply execute it on any SQL Server instance to be monitorized and the result will be another T-SQL script; this one will configure the accounts. This is the script:

SELECT
        'CREATE LOGIN [YOURDOMAIN\SCOM-SQLMPLowPriv] FROM WINDOWS' + char(13) + char(10) +
        'GO' + char(13) + char(10) +
        'CREATE LOGIN [YOURDOMAIN\SCOM-RA-SQL-Action] FROM WINDOWS' + char(13) + char(10) +
        'GO' + char(13) + char(10) +
        'GRANT VIEW SERVER STATE TO [YOURDOMAIN\SCOM-SQLMPLowPriv]' + char(13) + char(10) +
        'GRANT VIEW SERVER STATE TO [YOURDOMAIN\SCOM-RA-SQL-Action]' + char(13) + char(10) +
        'GRANT VIEW ANY DEFINITION TO [YOURDOMAIN\SCOM-SQLMPLowPriv]' + char(13) + char(10) +
        'GRANT VIEW ANY DEFINITION TO [YOURDOMAIN\SCOM-RA-SQL-Action]' + char(13) + char(10) +
        'GRANT VIEW ANY DATABASE TO [YOURDOMAIN\SCOM-SQLMPLowPriv]' + char(13) + char(10) +
        'GRANT VIEW ANY DATABASE TO [YOURDOMAIN\SCOM-RA-SQL-Action]' + char(13) + char(10) +
        'GRANT ALTER ANY DATABASE TO [YOURDOMAIN\SCOM-RA-SQL-Action]' + char(13) + char(10) +
        'GO' + char(13) + char(10)
UNION
SELECT
        'USE ' + name + ' ;' + char(13) + char(10) +
        'CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];' + char(13) + char(10) +
        'CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];' + char(13) + char(10) +
        'ALTER ROLE [db_owner] ADD MEMBER [SCOM-RA-SQL-Action];' + char(13) + char(10) +
        'GO' + char(13) + char(10)
FROM
        sys.databases WHERE database_id = 1 OR database_id >= 3
UNION
SELECT
        'USE msdb; EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername=''SCOM-SQLMPLowPriv''' + char(13) + char(10) +
        'GO' + char(13) + char(10)
UNION
SELECT
        'USE msdb; EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername=''SCOM-SQLMPLowPriv''' + char(13) + char(10) +
        'GO' + char(13) + char(10)

On a SQL Server Database Instance with the Operations Manager datawarehouse and reporting services databases this is the result of the execution, and this generated script will configure the instance:

CREATE LOGIN [YOURDOMAIN\SCOM-SQLMPLowPriv] FROM WINDOWS
GO
CREATE LOGIN [YOURDOMAIN\SCOM-RA-SQL-Action] FROM WINDOWS
GO
GRANT VIEW SERVER STATE TO [YOURDOMAIN\SCOM-SQLMPLowPriv]
GRANT VIEW SERVER STATE TO [YOURDOMAIN\SCOM-RA-SQL-Action]
GRANT VIEW ANY DEFINITION TO [YOURDOMAIN\SCOM-SQLMPLowPriv]
GRANT VIEW ANY DEFINITION TO [YOURDOMAIN\SCOM-RA-SQL-Action]
GRANT VIEW ANY DATABASE TO [YOURDOMAIN\SCOM-SQLMPLowPriv]
GRANT VIEW ANY DATABASE TO [YOURDOMAIN\SCOM-RA-SQL-Action]
GOUSE master ;
CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];
CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];
GO
USE model ;
CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];
CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];
GO
USE msdb ;
CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];
CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];
GO
USE msdb; EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM-SQLMPLowPriv'
GO
USE msdb; EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM-SQLMPLowPriv'
GO
USE OperationsManagerDW ;
CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];
CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];
GO
USE ReportServer ;
CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];
CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];
GO
USE ReportServerTempDB ;
CREATE USER [SCOM-SQLMPLowPriv] FROM login [YOURDOMAIN\SCOM-SQLMPLowPriv];
CREATE USER [SCOM-RA-SQL-Action] FROM login [YOURDOMAIN\SCOM-RA-SQL-Action];
GO