OpsMgr event ID 26319 and p_GetDatawarehouseVersion on SCOM 1801 – RESOLVED !!!
Hi SCOM folk
Today I am going to blog how to resolve event ID 26319 on System Center Operations Manager (SCOM) 1801 related to the p_GetDatawarehouseVersion stored procedure from getting generated.
The topic:
Looking at the Operations Manager event log on Management Server(s) from time to time, as any good SCOM Administrator should do, you will find some instances of the event ID 26319 (yeah, the well-known generic 26319 ).
What will surprise you is that among all these instances, there are some of them which have an interesting description. They report about the lack of permissions to execute a given stored procedure or that a stored procedure is missing. These instances of event 26319 get generated *only* on SCOM 1801 installations, every time the Operations Console is opened.
The issue:
As anticipated above, opening the Operations console will cause the generation of event ID 26319.
This event is generated with 2 different descriptions, depending on what type of installation has been carried out. That means that:
On new installations, the event is generated with this string in the description: The EXECUTE permission was denied on the object 'p_GetDatawarehouseVersion', database 'OperationsManagerDW', schema 'sdk'.).
On upgraded installations (from SCOM 2016), the event is generated with this string in the description: Reason. (Fault Detail is equal to Could not find stored procedure 'sdk.p_GetDatawarehouseVersion'.) .
The reason behind is that during the installation, the setup procedure for some unknown and strange reasons:
- Did not correctly set the permission for the sdk database schema on new installations
- Did not create the stored procedure, and of course did not set the permission, on upgraded installations
The solution:
Luckily, there is a solution for this mess. To resolve the issue and to stop the event ID 26319 from getting generated in both cases, follow the steps below:
Logon to the SQL Server host where the SQL Instance hosting the OperationsManagerDW is running
Open SQL Server Management Studio and connect to it
Expand Databases
à
OperationsManagerDW
à
Programmability
à
Stored Procedures
Scroll down, and search for the p_GetDatawarehouseVersion stored procedure
If the stored procedure is already there, meaning that this is a new installation, jump to step 13; otherwise continue to step 6.
Download the file attached to this post (taken from a new SCOM 1801 Installation) and save it locally on the SQL Server (i.e. in the C:\Temp folder or on the desktop). The file contains the following code:
In SQL Server Management Studio, click on File
à
Open à File
On the Open File windows, select the file downloaded in step 6 and click Open
Should you get the following message, click No
Once the file content is loaded, click Execute
Make sure the result pane is showing a success message
After the stored procedure is created, scroll up; right-click on Stored Procedures and click Refresh
From the stored procedures list, find and select the p_GetDatawarehouseVersion; right-click on it and select Properties.
In the Properties screen, select the Permission page
Click on the Search button and in the Select Users or Roles type the OpsMgrReader role name
Click on Check Names and then click OK
In the bottom part of the Permission page, check the box corresponding to Execute in the Grant column and click OK
The procedure above will fix the issue with the p_GetDatawarehouseVersion stored procedure and will stop the generation of event ID 26319 related to it … and will save you from headache
Thank you all and enjoy problem solving