Solved: The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’

Step 1: Connect the SQL Server using SQL Server Management Studio (SSMS).

Step 2: Click New Query from the Tool menu, paste below query and execute it.

USE master
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO

USE msdb
GO
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO

This query will grant the permission to report server database role (RSExecRole) to create a subscription job and schedule it. Now, you just try to create a new subscription on SSRS report and hope you can do it.

Software update group based Patch compliance report + SQL QUERY

Found useful so sharing it.

=======================================================================

SQL QUERY : 

Select name0 AS [Machine Name], sys.User_Name0, ui.Title AS [Patch Title], (CASE ucs.status WHEN 2 THEN 'Required' WHEN 1 THEN 'NOT REQUIRED' WHEN 0 THEN 'INSTALL STATE UNKNOWN' WHEN 3 THEN 'Installed' END) AS [Install Status] , dbo.v_AuthListInfo.Title as [Update Group],
ui.ArticleID, (CASE UI.IsExpired WHEN 0 THEN 'NOT EXPIRED' WHEN 1 THEN 'EXPIRED' END) AS [EXPIRY], (CASE UI.IsSuperseded WHEN 0 THEN 'NOT Superseded' WHEN 1 THEN 'Superseded' END) AS [SEPERSEDENCE] ,  (CASE ui.Severity WHEN 0 then 'none' when 2 then 'low' WHEN 6 then 'MODERATE' WHEN 8 THEN 'IMPORTANT' WHEN 10 THEN 'CRITICAL' END) AS [SEVERITY] , ui.DatePosted AS DateReleased, ucs.LastStatusChangeTime AS DateInstalled
FROM dbo.v_ClientCollectionMembers INNER JOIN
dbo.v_R_System AS sys ON dbo.v_ClientCollectionMembers.ResourceID = sys.ResourceID LEFT OUTER JOIN
dbo.v_CIRelation INNER JOIN
dbo.v_UpdateInfo AS ui INNER JOIN
dbo.v_UpdateComplianceStatus AS ucs ON ui.CI_ID = ucs.CI_ID ON dbo.v_CIRelation.ToCIID = ui.CI_ID INNER JOIN
dbo.v_AuthListInfo ON dbo.v_CIRelation.FromCIID = dbo.v_AuthListInfo.CI_ID ON sys.ResourceID = ucs.ResourceID
GROUP BY sys.Name0, ui.IsExpired, UI.IsSuperseded, ucs.status, ui.Severity, dbo.v_AuthListInfo.Title, dbo.v_ClientCollectionMembers.CollectionID, sys.User_Name0, ui.ArticleID, ui.Title, ui.DatePosted , ucs.LastStatusChangeTime
HAVING (ui.IsExpired = 0) AND
(dbo.v_AuthListInfo.Title like 'Name of SUG') AND (dbo.v_ClientCollectionMembers.CollectionID like 'collectionID')
ORDER BY sys.Name0
========================================================================

Ref: https://www.attosol.com/sccm-custom-sql-query-for-patch-compliance/

SCCM Distribution Point log Message Error 0x800706BA

Here is the way to fix this error. What you need to do is parse a MOF file on the target server on which you are installing the Distribution Point role. You need to look for smsdpprov.mof file which is found  in "<drive:>Program Files\Microsoft Configuration Manager\bin\X64" in your primary site server. Copy it to any drive or in a folder on the target DP server. Run the command prompt as administrator and execute the following command.

mofcomp.exe smsdpprov.mof

Note – MOF is a file extension for a Windows Management Object file format. MOF files created in the Managed Object Format have syntax based on Microsoft Visual C++.  MOF files can be compiled into the Windows Management Instrumentation (WMI) repository using mofcomp.exe.
In the below screenshot you can see that after running this command you see that MOF file has been successfully parsed.  This will create the WMI name space and after this step you should be able to install the DP without any issues. Don’t be afraid if it initially fails in the Distribution Point Configuration section in monitoring, just be patient and give it some time.

SCCM SQL Query to capture CPU information

SELECT
DISTINCT(CPU.SystemName0) AS [System Name],
CPU.Manufacturer0 AS Manufacturer,
CPU.Name0 AS Name,
COUNT(CPU.ResourceID) AS [Number of CPUs],
CPU.NumberOfCores0 AS [Number of Cores per CPU],
CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
FROM [dbo].[v_GS_PROCESSOR] CPU
GROUP BY
CPU.SystemName0,
CPU.Manufacturer0,
CPU.Name0,
CPU.NumberOfCores0,
CPU.NumberOfLogicalProcessors0

SCCM report for IIS installed with version details on servers

Hi all ,Another report to find out the servers where IIS is installed with the version of it.this assumes that ,you have enabled the inventory agent and set the properties for .exe to not exclude windows folder.If you select exclude files from windows directory,you will not see any results in the report because inetmgr.exe will be located in windows folder.

SQL SCCM query:

select distinct a.Name0,c.FileVersion from v_R_System a inner join

v_GS_SERVICE b on b.ResourceID = a. ResourceID inner join
v_GS_SoftwareFile c on c.ResourceID=a.ResourceID
where b.DisplayName0 like 'World Wide Web%' and
a.Operating_System_Name_and0 like '%Server%'  and
c.FileName ='inetmgr.exe'
Order by a.Name0,c.FileVersion 

Ref: http://eskonr.com/2010/05/sccm-report-for-computers-with-iis-installed-on-servers/

Setup was unable to compile the file DiscoveryStatus.mof The error code is 8004100E

SCCM Client installation issue and fix:

Error code: DiscoveryStatus.mof The error code is 8004100E

Just try this remediation step.

1. Open CMD in the administrator command prompt

2. Navigate to C:\Program Files\Microsoft Policy Platform

3. mofcomp ExtendedStatus.mof

4. Retry the CM client installation

Ref: https://social.technet.microsoft.com/Forums/en-US/1f48e8d8-4e13-47b5-ae1b-dcb831c0a93b/setup-was-unable-to-compile-the-file-discoverystatusmof-the-error-code-is-8004100e?forum=configmanagerdeployment

Default Software catalog website change to User friendly website name in SCCM 2012

Default Software catalog website change to User friendly website name in SCCM 2012

Purpose : 

              we created and published some applications in Software catalog in SCCM 2012, and during testing we had thought about create user friendly website instead default website which is lengthy

Default Site name: "http://<SCCM SERVER NAME>/CMApplicationCatalog"

User friendly site: "http://SelfServiceRequest"

How to achieve this:  we need to create DNS Alias name for SCCM Server and HTTP Redirect in SCCM server IIS.

Solution: 

1. Create DNS Alias name 

            Sccm server name : incrvsccm      Alias name: selfservicerequest

2. HTTP Redirect in SCCM IIS

            we should give http://SelfServiceRequest/CMApplicationCatalog

            

once done, click on "APPLY" button on right corner in IIS.

Now by clicking http://SelfServiceRequest will take to Software Catalog. 

========================================================

Read below article for exception:

Ref: https://thedesktopteam.com/raphael/sccm-2012-web-redirect-is-evil/

Hi All,

recently i was at a customer site performing the Build & Capture and it was failing when the Install Application step was invoked.

Customer was running WS2012R2, SCCM 2012 R2 with CU3, so the latest updates 

because we were doing the B&C for Windows 8.1, the hotfix for Windows 7 did not apply, so there problem had to be something else.

Looking at the client logs, I saw:

ClientLocation
[CCMHTTP] ERROR: URL=http://xxxxxx, PORT=80, Options=224, Code=0, Text=CCM_E_BAD_HTTP_STATUS_CODE

LocationServices
Workgroup Client is in unknown location
Failed to execute LSExecuteTask
[CCMHTTP] Error: URL=http://xxxxx, PORT=80, Options=224, Code=0, Text=CCM_E_BAD_HTTP_STATUS_CODE
Raising event: instance of CCM_CcmHttp_Status{ ………………..
Successfully sent location services HTTP failure message
Error sending HEAD request. HTTP code 401, status ‘Unauthorized’

CertificateMaintenance
MP xxxx does not allow client connections matching the client connection type

StatusAgent
HandleFSPCcmHttpStatus – Failed to retrieve assigned MP. Assuming ‘xxxxxx’ is not a relevant MP

So, looking at the IIS logs, I saw that before the setup windows the machine did connect properly to the MP folders (ie /SMS_MP/.sms_aut MPLOCATION, /ccm_system/request, SMS_MP/.sms_pol, etc) not once I see it connecting direct to the root /

However, once the client is installed, it start connecting to the root / and I see this as HEAD / and just after i was it being redirected to /CMApplicationCatalog with HTTP ID 302 but was getting HTTP error 401

Of course this is not normal, but this was happening because we did redirect the default IIS default website to /CMApplicationCatalog

This repeat few times per second, so at the end of the day, with 1 T&B TS, we got around 450MB of IIS log, not good, as this could fill up the drive quickly.

you may be asking, why this redirection? customer had a single server, single primary site. they have created a CNAME called InstallSoftware where the users could open the Internet and browse to http://InstallSoftware to get to the CMApplicationCatalog. as we don’t want users to see the IIS Default webpage and you know, users will not remember to add the /CMApplicationCatalog, we added the redirection…

Once we removed the redirection (that was done via a default.aspx file), it connects to the root / and get status 200, it them goes to ccm_system/request, sms_fsp, etc…fine

Speaking with MS, i asked why this request to the root / as this request doesn’t seem to be necessary and only cause extra network traffic (small, but extra…)

from the reply i’ve got, this behaviour was implemented on SP1 or R2 for specific workgroup scenarios to detect if the client is internet or intranet.

This still not make sense for me and i’ve submitted a DCR to have this behaviour changed, however, while speaking with MS, my fellow MVP Kim Oppalfens gave me the idea to test the IIS Url Rewrite module and i have to say that after testing it, this resolve the issue.

So, to get the redirection to the CMApplicationCatalog (or another website) without breaking your MP connection on a workgroup / OSD scenario, perform the following tasks:

1- Download the URL Rewrite (http://www.iis.net/downloads/microsoft/url-rewrite)
2- Install it (basic next-next-finish)
3- Open IIS and navigate to the default webiste
4- Select URL Re-write and add a new rule
4.1 – Under Match URL -> Using, change to Wildcards
4.2 – Under Match URL -> Pattern, type *
4.3 – Under Conditions, add:
4.3.1 – Condition Input -> {HTTP_USER_AGENT}, check if input string -> Does not match the pattern, Pattern -> SMS+CCM+5.0
4.3.2 – Condition Input -> {PATH_INFO}, check if input string -> Match the pattern, Pattern -> /
4.3.3 – Condition Input -> {REQUEST_METHOD}, check if input string -> Does not match the pattern, Pattern -> HEAD
4.4 – Under Action
4.4.1 – Action type -> Redirect
4.4.2 – Action Properties -> Redirect URL -> type /CMApplicationCatalog (or any other url you want)
4.4.3 – Action Properties -> check append query string
4.4.4 – Action Properties -> Redirection type -> Permanent (301)