Disabling RBAC during custom report creation

  • Article

A much anticipated feature of System Center Configuration Manager 2012 (SCCM) was RBA (Role Based Administration, also known as RBAC or Role Based Access Control).  When this functionality was added to reports it was very welcomed, but also made life a little difficult.  When most people, myself included, are working out the syntax of a report we want to work in SQL Management Studio.  If you take an existing report and try to customize it you will find that it is making a call to a RBAC function based on the user's SID.  You may not have a user SID available easily so you could remove that from the query syntax or look up a test SID or something.  There is an easier way.

If you look at the RBAC function you will notice that there are some exemptions if the passed in value is "disabled".  If you simply switch to use "disabled" in your testing you can keep the function but get full results, then when you are done working out your SQL syntax change it back to "@UserSIDs".

As an example, if I want to mess around with a report I already I have I start with the following query which I can grab from report builder, but won't work in SQL Management Studio :

select SYS.Netbios_Name0, TCU.SystemConsoleUser0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile  SF
join fn_rbac_R_System( @UserSIDs)  SYS on SYS.ResourceID = SF.ResourceID join v_GS_SYSTEM_CONSOLE_USER TCU on SYS.ResourceID = TCU.ResourceID
Where SF.FileName LIKE @variable
ORDER BY SYS.Netbios_Name0

For SQL Management Studio I use this variation to work from (also hard coding for another variable used in the query that is normally a prompted input):

select SYS.Netbios_Name0, TCU.SystemConsoleUser0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile  SF
join fn_rbac_R_System( 'disabled' )  SYS on SYS.ResourceID = SF.ResourceID join v_GS_SYSTEM_CONSOLE_USER TCU on SYS.ResourceID = TCU.ResourceID
Where SF.FileName LIKE 'cmtrace.exe'
ORDER BY SYS.Netbios_Name0