Why Is It Important to Use Supported SQL Server Views with SCCM Reporting?

While I was at the Midwest Management Summit at the Mall of America (MMSMOA) in May, the topic of supported SQL Server views came up. Someone mentioned an easy way to convert WQL queries to SQL queries. This “tip,” however, did not create supported SQL Server views and not everyone seemed to realize that this was a bad idea. In order to shed more light on this topic, I’m providing some background in this post on why you must only use supported SQL Server views and, more importantly, what happens when you don’t use them.

If you want to see what SQL Server views and functions are supported by the SCCM product team, take a look at this blog post that I published shortly after MMSMOA, “What Are the Supported SQL Server Views to Use with SCCM Reporting?”

Why Are Supported SQL Server Views Important?

There are three major reasons why you always want to use supported SQL Server views and functions, and you should steer clear of the unsupported ones with System Center Configuration Manager (SCCM) reporting.

They are:

1. Transaction Locking

2. Granting dbo Permissions to Users

3. Changing Permissions on a Table/View/Etc.

Transaction Locking

SCCM stores data in the SQL Server database and one issue that can arise when data is accessed from the SQL Server database is called, “Transaction Locking.” Why is this important to an SCCM Administrator and in particular to SCCM reporting? Here’s the simple answer: when an unsupported SQL Server query is executed for a report, it creates a lock on the table that is being queried. This lock MAY block SCCM itself from updating or inserting data into the database, or it may even block other queries.

For more information, see this Stack Overflow post, Understanding SQL Server LOCKS on SELECT queries.

The Microsoft docs (see link below) are also good at explaining this problem:

In any database, mismanagement of transactions often leads to contention and performance problems in systems that have many users. As the number of users that access the data increases, it becomes important to have applications that use transactions efficiently.

Later on, under Locking and Row Versioning Basics, the docs say:

Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock. Each transaction frees its locks when it no longer has a dependency on the locked resources.

In a simplified nutshell, all of this is saying that no two queries can use the same object at the same time. For more details please see the Microsoft SQL Server docs: Transaction Locking and Row Versioning Guide.

Transaction Locking Solution

In order to solve this problem (Transaction Locking) the SCCM team uses the (Nolock) query hint within supported SQL Server views. The (Nolock) query hint prevents this issue from occurring.

Permissions

Has this ever happened to you before? You create a report with unsupported SQL Server views, test it and then upload it to your SSRS site. It works fine for you, so you give the report to someone else and, “uh-oh,” they run it and get the following message:

• An error has occurred during report processing. (rsProcessingAborted)
  • Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)
    • The SELECT permission was denied on the object ‘vSMS_Advertisement’, database ‘CM_CB1’, schema ‘dbo’.

Once you determine that it is a permission problem, you perform one of two bad ideas. You either grant dbo permissions to a user or change permissions on a table/view/etc.

Granting DBO Permissions to a User

By default, only the database owner (DBO) or System Administrator (SA) accounts within SQL Server have access to all objects within the database. Since unsupported SQL Server views, functions, etc., have no permissions assigned to them, only accounts designated as DBO or SA can access the data from those views, tables, etc. Therefore, if you want a low-rights user to see the data within the views, tables, etc. one of your options is to grant them SA or DBO rights on the database.

Obviously, this is a bad idea due to the inherent security risks. The one “upside” is that this method has no effect when SCCM is upgraded to a higher version at a later date.

Changing Permissions on a Table, View, Etc.

Your other option is less risky, but still has security issues associated with it. You can simply adjust the “select” or “execute” permissions on the views, tables, etc. Although this method is less of a security risk, it might prevent you from upgrading to a newer version of SCCM at a later date. Why? Editing the SCCM database is not supported. Plus, do you really want to change the permissions on all of the views, tables, etc., that you want to use?

Supported SQL Server Views

What are the benefits of using supported SQL Server views and functions with SCCM reporting? Besides avoiding the aforementioned transaction locking and permission issues, here are a couple of more reasons:

· Role-Based Administration (RBA). You can leverage RBA for queries. This means that if someone shouldn’t see data, with an RBA report, they won’t see it!

· Performance Tweaks. Microsoft is putting a lot of effort these days into tweaking SQL Server, so the performance is faster and better! In this regard, they are only updating supported SQL Server views. For that matter, RBA functions are where they are putting most of their effort. You can see what I mean in this post, “RBA and Non-RBA Queries: When is Slower Actually Faster?”

What SQL Server views and functions are supported by Microsoft? The short answer is any SQL Server object with “select” or “execute” permissions assigned to the smsschm_users role. For the complete list, see my blog post, “What Are the Supported SQL Server Views to Use with SCCM Reporting?”

If you have any questions about supported SQL Server views and functions, please feel free to contact me at @GarthMJ.