El Blog de Gualtrysoft

Windows 2000/2003/2008, Active Directory, VBScript, Hyper-V, PowerShell y todo aquello interesante a la hora de usar, configurar y administrar Windows Server. También tenemos longanizas…

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

Posted by urpiano en Martes 23 \23\UTC febrero \23\UTC 2016

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

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s