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…

Archive for 23 febrero 2016

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

Posted by urpiano en Martes 23 \23\+01:00 febrero \23\+01:00 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

Posted in Bases de datos, Informatica, SCOM, System Center | Leave a Comment »