I hope this is the correct place to put this question.
I’ve been trying to figure out how to extract users & their assigned user groups from our CMS tables, in Microsoft SQL server mangement studio, I think I’ve narrowed it down to CMS.InfoObjects7, but i can’t really seem to find anyway to link the user & the usergroups that are stored in it? And the CMS.RELATIONS7, doesn’t seem to have any link either. Given the data is encrypted, it has been a little difficult to interpret what exactly the data is.
Does anybody know an SQL statement that would link or how the database schema links the information of users & their assigned user groups? or how to de-encrypt the data?
I cannot answer your question directly, but I can provide some alternative suggestions.
You can get this information for Query Builder, but the results are difficult to consume (e.g. cannot export cleanly). You can use CMS Query Builder, a free tool from biclever, to run Query Builder queries and be able to export them. I have used this method.
You could also use the BI platform CMS system database.unx discussed here and here. I have not used method. From a past post on this forum I believe @JohnBClark has and may be able to provide some more helpful guidance.
I haven’t attempted to pull any user and group information using the BI platform CMS system database but I have seen examples for pulling that type of data, although I can’t remember where.
The bigger problem is that Data Services doesn’t come with the universe or any reporting tools so this may not be an option.
@BMBT , you won’t be able to query the tables directly as the information stored there is encrypted. For a Data Services installation, you will most likely need to use an SDK interface. I don’t think any of the Excel extractors available here would work for you either as they are reliant on the SDKs downloaded and installed with the reporting tools. This means that you would probably need to write your own interface.
The biclever tool might be your best option because you could then at least export that results into Excel to manipulate.
