ConfigMgr
Editing ConfigMgr / SCCM Reports with Report Builder
Topics: ConfigMgr
Did you know that you need a Role-Based Administration (RBA) security role when you want to edit Microsoft Configuration Manager (ConfigMgr) Current Branch or 2012 R2 reports with Report Builder? You do! In this post, I’ll show you how you can start editing ConfigMgr / SCCM reports with Report Builder.
SQL Server Data Tools
Before getting started, what if you only want to create and edit reports with SQL Server Data Tools (SSDT)? In this case you don’t really need to create a SCCM Report Editor security role, but you will still need to grant your report editors access to the SQL Server and the SCCM database. I think, though, that it’s always a good idea to create the security role because it will help with documenting who can create or edit reports (whether they are SSRS or Power BI) for your company. Plus, if you (the SCCM Admin) have a security role, you can install and quickly edit reports using Report Builder when SSDT isn’t handy.
Security Role
Back in 2016 I created a SCCM security role with the Edit Report security option enabled for each instance. Here’s the download. Even though the URL says, “cm12,” it is still good to use with SCCM Current Branch. This SCCM security role will allow you, the SCCM Admin, to grant rights to an Active Directory (AD) security group which will allow them to edit and/or download existing reports from SCCM. The SCCM security role, however, will NOT grant your SCCM report editors access to the SCCM database or the SQL Server.
In order to give your SCCM report editors access to the SQL Server and the SCCM database, I recommend granting an AD security group access to the SQL Server and then giving this group smsschm_users rights to the SCCM database. This will allow your users (AD security group) access to query the supported objects within the SCCM database, BUT they will NOT be able to edit anything or see objects that they don’t have access to within the SCCM database.
Below I’ll show you how to grant an AD security group access to the SQL Server and assign it smsschm_users rights.
Granting SQL Server Security Rights
Start by opening SQL Server Management Studio (SSMS) and connecting to your SCCM SQL Server. Next, expand the Security node. Right-click on the Logins node, and select New Login…
Click on the Search… button.
Select the Active Directory (AD) group that will be used for assigning permissions and then click OK.
Select the User Mapping node.
Select your SCCM database (in my case it’s CM_CB1) and then in the lower window, select the checkbox next to smschm_users. Click on the OK button in order to apply the security settings. With this step complete, you gave the AD security group rights to logon to the SQL Server and you granted this group the appropriate permissions to access the SCCM database.
Importing the SCCM Report Editor Security Role
Make sure to import the SCCM security role and assign the AD security group to that SCCM security role. Here’s the download.
By the way, this security role is very similar to the SCCM Report Reader security role I created (also in 2016).
Start Editing SCCM Reports with Report Builder
Now that you finished granting the AD security group access to the SQL Server, assigned it smsschm_users rights and assigned the security role to it, you are ready to start editing SCCM reports with Report Builder.
Keep in mind that I strongly recommend that you don’t create reports directly against the production database. The reason is simple, a wayward query can, “kill,” your SQL Server and it might even effect your clients. You should always create your SSRS or Power BI reports/dashboards in a development environment.
Within the SCCM console, under the monitoring node, select Overview, Reporting, and Reports. Next, locate the report you want to edit. Right-click on it and select Edit. Now you’re ready to go!
Error: Report Builder not available
If you receive the following error message:
Before you can create or edit reports, your computer must run a version of SQL Server Report Builder that matches the version of SQL Server that you use for your report server.
It can have two meanings.
1. You need to manually install Report Builder on your computer.
2. This is probably less likely, but it could mean that you have two or more Reporting Points and the SQL Server versions don’t match. This happens to me a lot due to my setup, but for most this will be rare.
Stay tuned because in a couple of weeks I will have a new post with more reporting tips!
If you have any questions about how you can start editing ConfigMgr / SCCM reports with Report Builder, please feel free to contact me @GarthMJ.
Additional ConfigMgr / SCCM Resources
Learn more about how to better use ConfigMgr / SCCM.
Overview
Inventory
Reporting
- How Can I Install Report Builder?
- How to Install a SCCM Reporting Services Point
- Dynamic Images to SSRS Report for SCCM
- Fixing the SCCM Reporting Services Point
Scripting
Security/Permissions
Software
External Integration
