Last updated on August 2nd, 2022 at 09:22 am
I write a lot of queries for Configuration Manager 2012 (CM12) and Configuration Manager 2007 (CM07). I also use SQL Server Management Studio (SSMS) to test all of my queries before I create SSRS reports. Now that CM12 R2 is released, I still want to test all of my queries in SSMS first using the Role-Based Administration (RBA) feature as it would work within SSRS. I wasn’t quite sure how to do this, so I spoke to the Microsoft team and they gave me some helpful tips on how to write the reports for RBA. However, I still didn’t know how to test my queries first in SSMS.
By accident, while I was learning how to write reports to leverage RBA, I found a way to test my queries in SSMS! Now I would like to share my discovery with you, so that you don’t have to overcome the same hurdles as I did.
In order to use RBA within your CM12 R2 SSRS reports you need to take the following steps:
- Determine the user’s account tokens (SIDs).
- Convert those SIDs to a CM12 UserSID.
- Write your queries to use this UserSID.
It sounds simple enough, but how do you test this within SSMS?
Take it from me, Step #2 and Step #3 are easy enough once you know the secrets. However, Step #1 is a bit of a headache.
Test Your Role-Based Administration
Step #1
How do you find the access tokens for a given user so that you can test RBA within SSMS? We know that within SSRS reports, you need to add an option to each report to capture this data. It is collected by executing, SrsResources.UserIdentity.GetUserSIDs(User!UserID), within the prompts.
Using this information, I created a new SSRS report to display the SID.
You can see above that the User SIDs is listed. You can download the SSRS report shown above (RDL) from here:
https://askgarth.com/wp-content/uploads/2022/08/SIDDetails.zip
Copy the User SIDs and paste it into a variable called @UserTokenSID in SSMS.
Step #2
The secret to this is to execute the following query to get the CM12 User SID once you know the User SIDs. Simple enough?
set @UserSIDs = (select dbo.fn_rbac_GetAdminIDsfromUserSIDs(@UserTokenSIDs) )
Step #3
Pass the @UserSIDs to the appropriate function. Again simple enough.
dbo.fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs)
Putting All the Steps Together:
What does it all look like in the end within SSMS?
Declare @UserTokenSIDs as varchar(max)
Declare @UserSIDs as int
/* Morgan */
set @UserTokenSIDs = ‘S-1-5-21-2500911358-3031180111-1296354870-1138,S-1-5-21-2500911358-3031180111-1296354870-513,S-1-1-0,S-1-5-21-3924082089-2959414220-3753022234-1009,S-1-5-21-3924082089-2959414220-3753022234-1010,S-1-5-32-545,S-1-5-2,S-1-5-11,S-1-5-15,S-1-5-21-2500911358-3031180111-1296354870-1277,S-1-18-2’
set @UserSIDs = (select dbo.fn_rbac_GetAdminIDsfromUserSIDs(@UserTokenSIDs) )
Select
CS.Name0
From
dbo.fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) CS
order by
CS.Name0
Finally, to prove that I am using Role-Based Administration (RBA), below is a screenshot showing the results using RBA and without using RBA. Notice that my test admin account, Morgan, can see twice as many PCs in the second query as he can in the first query. (Yes, this lab only has two PCs in it at this time. :-))
