Forum Discussion

alexyin1053's avatar
alexyin1053
Helper I
1 year ago
Solved

Get SQL Server Data from Power BI Report Server's Rest API

Dear all,   Currenly we're running on premise power bi report server, while we would like to get one of SQL Server's  table([ReportServer].[dbo].[Users]) by using Power BI Report Server's Rest API....
@NeedHelp
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, alexyin1053 

    Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:

    key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn

    key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn

    import requests
from requests_ntlm import HttpNtlmAuth

url = "http://Yourserver/Reports/api/v2.0/PowerBIReports"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# replace your username and pwd
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
reportlist = response.json()["value"]
newlist = []
newdict = {}
for item in reportlist:
    newdict = {"id": item["Id"],"ReportName":item["Name"]}
    newlist.append(newdict)
for i in range(len(newlist)):
    url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies"
    response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password))
    policies = response1.json()["Policies"]
    newlist[i]["Policies"] = policies
    

print(newlist)

    You should be able to get a dictionary like this (which contains a list of user information accessible for each report):

    You can then use this dictionary for analysis or import into Power BI.

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

alexyin1053's avatar
alexyin1053
Helper I
1 year ago

Hi rohit1991 ,

 

Huge thanks for your reply, which helped us better understand what PBIRS can actually do.

Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
SSMS Query SQL code:

 

SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;

 


Ideally, we would like to retrieve the upper information directly through the API of the AP Server.
Besides, we also have access to the SQL database, executing it directly via PowerShell or Python scripts is also a viable option.

Let me know if more information is needed!

Thanks!

 

Best Regards,

Alex

rohit1991's avatar
rohit1991
Super User
1 year ago

Hi alexyin1053 

 

It's great that you have access to both the Power BI Report Server (PBIRS) REST API and the SQL Server database. Unfortunately, the PBIRS REST API does not provide direct access to the [ReportServer].[dbo].[Users] table or user permissions in a structured way. However, since you have SQL access, the best approach is to execute your SQL query directly via PowerShell or Python to retrieve the required user and report details. You can use PowerShell's Invoke-Sqlcmd or Python's pyodbc or SQLAlchemy to run your query and return the results in a structured format. If you still prefer using the PBIRS API, you can use the /api/v2.0/CatalogItems endpoint to get reports and /api/v2.0/Folders to explore folder structures, but user permission details would still need to come from the database.