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 the ‘Bases de datos’ Category

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

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

Oracle 11gR2 y la query del sudoku

Posted by calabuch en Miércoles 11 \11\UTC agosto \11\UTC 2010

Es raro encontrar  código de Oracle que se pueda aplicar inmediatamente al mundo de la no-informática, en este caso hemos encontrado una query que resuelve un sudoku. Lo mejor de esta consulta es que cumple la máxima : “No hagas en un bloque (pl/sql) lo que puedas resolver en una sola consulta.”, bueno, decir que la cumple es poco, realmente es un ejemplo excelente de esta directriz.

Leer el resto de esta entrada »

Posted in Bases de datos, Informatica, Oracle | 1 Comment »