SCCM-Running RBAC queries on SQL Mgmt

SCCM-Running RBAC queries on SQL Mgmt
Rate this post

Hi All,

SCCM 2012 R2 introduced the RBAC to reports, what I mean by that is that now, users will be only able to see what they have rights to see. This is done by the introduction of the @UserID parameter to the reports.

This parameter is hidden, so you don’t even know that it is there. however, if you try to run a report query on a SQL Management Studio, it will not like. As workaround, you can change the @UserID for ‘disabled’ and it will not validate the security.

More info at http://blogs.technet.com/b/configmgrdogs/archive/2014/07/14/creating-custom-rbac-enabled-reports-in-configmgr-2012-r2.aspx

as you can imagine, it is really good, you can get any report query and change the @UserID for ‘disabled’ and test the query, or do the other way around, however, this is not good if you want really know what is returning is what it should return, is it?

So, imagine an example:
Query1: select * from V_R_System
This is the query that you normally do on the Sql Management studio, it does return all the Devices without any filter (or security filter)

Query2: select * from fn_rbac_R_System(‘disabled’)
similar to the other query, it will bring all devices as the security filter is being disabled.

Query3: select * from fn_rbac_R_System(@UserID)
This query will not work and the SQL will return the error: Must declare the scalar variable “@UserID”.

so you declare
declare @UserID varchar(max) and when you ran the query, it does not return anything because you did not pass anything to the @UserID..so you cannot test it properly.

to “fix” this problem, you need to get the proper @UserID so, this is the way to do:

1- Add a permission to the user that is running the query. He/she should have the smsschm_users on the SCCM Database
sql01

2-use the SQL Code below to declare and set the variables needed

 

3-use the @UserID without any problem and be happy creating and testing your reports

Tags:

Recent Posts

Comments are closed.
%d bloggers like this: