SCCM Reporting - "SELECT permission was denied on the object"

Sharing you my experience on resolving the SELECT permission error while running a SCCM Web Report. 

Sometimes SCCM may throw an error while running a new report on Web Browser. The same report may work just fine on the SQL server reporting console, but not on web browser.

Detailed error on "Select permission" that appears on the Web browser:
The SELECT permission was denied on the object 'xxxxxxxxx', database 'SMS_ABC', schema 'dbo'.
Error Number: -2147217911
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 229  

I then referred to some online articles for troubleshooting and solved the issue by adding the SELECT permissions for the SQL View on SQL server.

Added below the resolution snapshots for your reference:

Step 1 - SELECT permission error on SCCM Report

Step 2 - View properties - adding permission

Step 3 - Browse and add Database role "smsschm_user"

Step 4 - Choose Select permission for smsschm_user

Step 5 - Add "webreport_approle" and choose Select permission

Step 6 - SCCM Web Report works fine

SQL Joins - INNER, OUTER, LEFT and RIGHT

Here is a simple post from Eswar explaining why and where to use SQL join. Please check the below link to view the post:

Writing SCCM Reports using SQL Joins

http://eskonr.com/2012/08/writing-sccm-reports-using-sql-joins/

For more detailed information on SQL joins, Check the below post by Suresh Dasari:

Joins in sql server, Inner Join, Cross Join, Left Outer Join, Equi join, Right Outer Join, Full Outer Join

http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html

Unable to create collection on SCCM 2007 / Logs not updating

Would like to share one of the weird issues we faced in our SCCM infra and their technet forum links with solution. Hope you all SCCM Folks will find it useful..

Unable to create any new collection on SCCM 2007
-- http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/1b38b94a-db80-4e48-bda1-94af825eb78e
 
One fine day at my office, ConfigMgr 2007 reported an error while trying to create any new collection.

Error Detail: “Form that is already visible cannot be displayed as a modal dialog box. Set the form's visible property to false before calling ShowDialog.”

Click the below forum link for more details and resolution: 
http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/1b38b94a-db80-4e48-bda1-94af825eb78e
...
...
Troubleshooting the SCCM'07 Collections issue lead to a new problem due to a regedit step which was not reverted back after testing.

ConfigMgr 2007 Logs not updating
-- http://social.technet.microsoft.com/Forums/en-US/configmgrsum/thread/c9d2a699-e822-4249-b317-8873d7b56e9b
 
Site Reset and Site Repair did not help, and finally found that the Tracing was disabled in registry.

Check the below forum link for more details and resolution: 
http://social.technet.microsoft.com/Forums/en-US/configmgrsum/thread/c9d2a699-e822-4249-b317-8873d7b56e9b

Ref: http://social.technet.microsoft.com/Forums/en-US/user/threads?user=ucrajee

SCCM and SQL Reporting Services

   With the R2 release, SCCM now supports the use of SQL Reporting Services as a reporting solution.  This will be the direction for reporting going forward and offers a good number of exciting possibilities for building impactful custom reports.  In the January edition of Technet magazine Steve Rachui published an article discussing this feature and showing a sample method for building custom reports.

Create a Robust, Integrated Reporting Solution - http://technet.microsoft.com/en-us/magazine/ee914611.aspx

Difference between WQL and SQL in SCCM

Below are few points which make difference between WQL and SQL :

1. WMI provides its own query language that allows you to query managed objects as data providers
2. WMI Query Language (WQL) is essentially a subset of SQL (Structured Query Language) with minor semantic changes.
3. Unlike SQL, WQL does not provide statements for inserting, deleting, or updating data and does not support stored procedures.
4. WQL does have extensions that support WMI events and other features specific to WMI. WQL is the basis for Configuration Manager queries, whereas SQL is used for ConfigMgr reports.
5. One important advantage of WQL is that a WQL query can return WMI objects as well as specific properties.
6. Because management applications such as the Configuration Manager console interact with WMI objects, WQL queries can return result sets that you can use within the ConfigMgr infrastructure.

Eg: Configuration Manager collections are based on WQL queries.

Ref: http://systemcentersupport.blogspot.com/2010/01/what-is-wmi-query-langauge-or-wql.html