Setting Up Security for SCCM Power BI Reports

What do I mean by, “Setting up security for SCCM Power BI reports?” First, in order to create and run Power BI reports, you need to access the SQL Server database directly from Power BI Desktop, so you need to pay attention to who can access this data. Second, only supported SQL Server views, functions, etc., should be used with Power BI reports. If not, there are a whole host of issues that can follow.

Why am I focusing on those two points? Most Power BI reports are written by non-SCCM/SQL Server people. Let’s face it, that’s part of the allure to Power BI. The downside is that some non-SCCM/SQL Server folks are granted dbo or db_datareader rights to the SCCM SQL Server database. Once given this all-access pass to SCCM data, however, a non-Admin may not know what SQL Server objects to use.

How, then, can you restrict users from using unsupported SQL Server views and functions in their Power BI reports? I answer that question in this post by showing you a quick and simple trick! Limiting what the end user can see to only Microsoft SCCM-supported objects, gives some assurance that they won’t create a headache for you later on.

SQL Server Views and Functions

Microsoft ONLY supports querying SCCM SQL Server views and SCCM SQL Server table-value functions. NOT all SQL Server views and functions, however, are supported.

Only SQL Server views and functions that have smsschm_users “Select” or “Execute” permissions are supported with reporting. It doesn’t matter if you’re using Power BI, SSRS or any third-party tools.

The simplest way to restrict what an end user sees in the SQL Server database is to leverage the SCCM Report Editor security role. Instructions about how to do this are found in the, “Granting SQL Server Security Rights,” section of this post, How to Start Editing SCCM Reports with Report Builder. This post doesn’t really say anything about Power BI, but everything in it applies to Power BI.

Granting SQL Server Security Rights for SCCM Power BI Reports

Take it from me that this is a simple task! Here is a summary of the steps:

• Create an AD security group called SCCM Report Editors.
• Create a SCCM Report Editors security role (optional, but I recommend it).
• Assign the SCCM Report Editors security role to the AD SCCM Report Editors security group (optional, but I recommend it).
• Within SQL Server, create a new SQL Server login for the AD SCCM Report Editors security group.
• On the SCCM database, grant the SQL Server login, for the AD SCCM Report Editors security group, the database role of smschm_user.

Here’s a quick note about the two optional steps. You might be asking yourself, “Why create them?”

There are two main reasons:
-First, it allows you to edit the built-in SSRS reports using Report Builder via the SCCM console.
-Second, and more importantly, to document who has access to the SCCM database. Although you can find out who has access to the database via SQL Server itself, it is not always obvious from within SCCM, so these steps help save you some time and effort in the long run.

Again, for more information about how to perform these steps, see the instructions found in the, “Granting SQL Server Security Rights,” section of this post, How to Start Editing SCCM Reports with Report Builder.

SQL Server Security for SCCM Power BI Reports

Let’s see what happened after I performed the above-noted steps on my test account. Looking at the screenshot below, on the left-hand side you can see the number of objects that my SQL Server Admin account can access. On the right-hand side you can see the number of objects that I can use within SCCM reporting with my restricted account. What a big difference!

Using Unsupported SQL Server Views

You can read more about SCCM supported and unsupported SQL Server views, tables, etc., in a couple of posts I published earlier this year: What Are the Supported SQL Server Views to Use with SCCM Reporting? and Why Is It Important to Use Supported SQL Server Views with SCCM Reporting?

As an aside, I always recommend writing queries first in SQL Server Management Studio (SSMS) before even opening up Power BI. If you like, you can use the Query Designer in SSMS. However, I know from reading posts in the forums and from experience that most people ignore this advice. Why? Either they don’t know any better or they think it is more “convenient” to simply open Power BI. For now, I won’t address why starting with SSMS is better because this security issue (seeing all of the views/tables/etc.) can’t be avoided there either without assigning the correct SQL Server permissions as spelled out in this blog post.

If you have any questions about setting up security for SCCM Power BI reports, please feel free to contact me at @GarthMJ.