Identify User or Group Provisioning direct from SQL

Have you ever needed to identify either what groups or users were provisioned for an application? You could go through the provisioning reports generated by Hyperion Shared Services, but these are sometimes not easy to read and definitely not formatted easily.

Using the SQL here, you can pull the details from SQL Server and the results to a CSV or Text file for easier reading.

The only item you need to identify is the name of the application. To find this do the following on the SQL Server.

1. Open SQLServer Management Studio
2. Find the database used when configuring Shared Services (Example would be EPM11_HSS)
3. Expand the above database to the Tables folder. Under the Tables locate the dbo.CSS_PROVISIONING_INFO table
4. Right click and choose "Select Top 1000 Rows. This will show the results at the bottom. You can change the query from Select Top 1000 to say 5000 or more if very large. You will see the results on the bottom.
5. In the first column under Application_ID you will see the names of the applications. Scroll down until you find what you are looking for. Examples are for Essbase will be ESBAPP:DEMO_EssbaseCluster-1, FDM will be FDM:APPNAME, HFM will HFM:HFMAPPNAME, Planning will be HP:PLANAPPNAME
6. Once you have the application name go to a new query in SQL and paste this SQL Query in. You will need to change the Type of Application and AppName

select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE FROMCSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY) and CSS_PROVISIONING_INFO.APPLICATION_ID='HFM:APPNAME' and CSS_ROLE_LOCALES.locale='en' AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1 AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY) 

7. Click Execute and on the bottom it will list the users who are provisioned.

8. If you provision by Groups, in your query just replace the CSS_USERS.NAME in the first line with select CSS_GROUPS.Name and execute the query.

9. After the query shows the results, do a File-Save As and save as .CSV and you can then open in a text editor or Excel for easier viewing.