The following SQL script should set up a local LPU user and grant the required SELECT permissions to the necessary tables in order to collect data from the source database. You may choose to use an AD user account instead of a local as indicated in the script.

Take note of the following placeholders within the script:

  • Replace LPUUSERNAME with DB username for LPU user

  • Replace LPUPASSWORD with LPU user password

  • Replace LPUROLE with the name you want to assign to the LPU role

Note: If the operational database is on a separate system than the Data Warehouse database, those sections of the script must be run on the correct corresponding systems.

-- Run the code below on ALL database Servers used by SCOM

use master;

CREATE LOGIN LPUUSERNAME WITH PASSWORD = LPUPASSWORD;

CREATE USER LPUUSERNAME;

-- If using AlwaysOne Availability Clusters, Need to record SID and create user with that

--SELECT [name], [sid]

--FROM [sys].[database_principals]

--WHERE [type_desc] = 'SQL_USER' AND

--[name] = 'LPUUSERNAME'

-- On other servers

-- CREATE LOGIN LPUUSERNAME WITH PASSWORD = LPUPASSWORD,SID = <Full SID here, should be long hex number starting with 0x>

GRANT CONNECT SQL to LPUUSERNAME;

GRANT VIEW SERVER STATE to LPUUSERNAME;

GRANT VIEW DATABASE STATE TO LPUUSERNAME;

GRANT VIEW DEFINITION TO LPUUSERNAME;

-- Run the following code on the OperationsManager Database Servers

use OperationsManager;

CREATE USER LPUUSERNAME;

GRANT CONNECT to LPUUSERNAME;

GRANT VIEW DATABASE STATE TO LPUUSERNAME;

GRANT VIEW DEFINITION TO LPUUSERNAME;

CREATE ROLE LPUROLE;

EXEC sp_addrolemember N'LPUROLE', N'LPUUSERNAME'

GRANT SELECT ON dbo.ManagedEntityGenericView TO LPUROLE;

GRANT SELECT ON dbo.ManagedTypeView TO LPUROLE;

GRANT SELECT ON dbo.RelationshipGenericView TO LPUROLE;

GRANT SELECT ON dbo.RelationshipTypeView TO LPUROLE;

GRANT SELECT ON dbo.PerformanceDataAllView TO LPUROLE;

GRANT SELECT ON dbo.PerformanceCounterView TO LPUROLE;

GRANT SELECT ON dbo.AlertView TO LPUROLE;

GRANT SELECT ON dbo.MaintenanceModeView TO LPUROLE;

GRANT SELECT ON dbo.MTV_Computer TO LPUROLE;

GRANT SELECT ON dbo.RuleView TO LPUROLE;

GRANT SELECT ON dbo.ManagementPackView TO LPUROLE;

IF EXISTS (SELECT * FROM sys.views where name = 'MTV_Microsoft$Windows$HyperV$VirtualMachine')

GRANT SELECT ON dbo.MTV_Microsoft$Windows$HyperV$VirtualMachine TO LPUROLE;

IF EXISTS (SELECT * FROM sys.views where name = 'MTV_Microsoft$SQLServer$DBEngine')

GRANT SELECT ON dbo.MTV_Microsoft$SQLServer$DBEngine TO LPUROLE;

-- Grant SELECT access to all tables matching MTV_%NetworkAdapter

DECLARE @NetworkTableName AS nvarchar(128);

DECLARE @NetworkTableCursor AS CURSOR;

SET @NetworkTableCursor = CURSOR FOR

SELECT name FROM sys.views where name LIKE 'MTV_%NetworkAdapter';

OPEN @NetworkTableCursor;

FETCH NEXT FROM @NetworkTableCursor INTO @NetworkTableName;

WHILE @@FETCH_STATUS = 0

BEGIN

--GRANT SELECT ON dbo.[@NetworkTableName] TO LPUROLE;

DECLARE @sql AS varchar(max);

SET @sql = 'GRANT SELECT on dbo.' + @NetworkTableName + ' TO LPUROLE;';

EXEC(@sql);

FETCH NEXT FROM @NetworkTableCursor INTO @NetworkTableName;

END

-- Run the following code on the OperationsManagerDW Database Servers (Data Warehouse)

use OperationsManagerDW;

CREATE USER LPUUSERNAME;

GRANT CONNECT to LPUUSERNAME;

GRANT VIEW DATABASE STATE TO LPUUSERNAME;

GRANT VIEW DEFINITION TO LPUUSERNAME;

CREATE ROLE LPUROLE;

EXEC sp_addrolemember N'LPUROLE', N'LPUUSERNAME'

GRANT SELECT ON dbo.vManagedEntity TO LPUROLE;

GRANT SELECT ON dbo.vManagedEntityType TO LPUROLE;

GRANT SELECT ON State.vStateRaw TO LPUROLE;

GRANT SELECT ON dbo.vRelationship TO LPUROLE;

GRANT SELECT ON dbo.vRelationshipType TO LPUROLE;

GRANT SELECT ON dbo.vRelationshipProperty TO LPUROLE;

GRANT SELECT ON dbo.vRelationshipTypeManagementPackVersion TO LPUROLE;

GRANT SELECT ON perf.vPerfRaw TO LPUROLE;

GRANT SELECT ON dbo.vPerformanceRuleInstance TO LPUROLE;

GRANT SELECT ON dbo.vPerformanceRule TO LPUROLE;

GRANT SELECT ON Alert.vAlert TO LPUROLE;

GRANT SELECT ON Alert.vAlertResolutionState TO LPUROLE;

GRANT SELECT ON Event.vEvent TO LPUROLE;

GRANT SELECT ON dbo.vDisplayString TO LPUROLE;

GRANT SELECT ON dbo.vMaintenanceMode TO LPUROLE;

GRANT SELECT ON dbo.vMaintenanceModeHistory TO LPUROLE;

GRANT SELECT ON dbo.vManagedEntityTypeProperty TO LPUROLE;

GRANT SELECT ON dbo.vManagedEntityPropertyset TO LPUROLE;

GRANT SELECT ON dbo.vRule TO LPUROLE;

GRANT SELECT ON dbo.vMonitor TO LPUROLE;

GRANT SELECT ON dbo.vManagementPack TO LPUROLE;

GRANT SELECT ON dbo.vManagedEntityMonitor TO LPUROLE;

GRANT SELECT ON dbo.vTypedManagedEntity TO LPUROLE;

GRANT SELECT ON Event.vEventRule TO LPUROLE;