Forum Discussion

Abdelmajid's avatar
Abdelmajid
Helper I
5 years ago

Help with PBIRS ReportServer queries

Hello,

 

I need some help please with the following queries on Power BI reports server (PBIRS):

 

1-Query the list of Active Directory groups used in manging reports' permissions and the users within each group.

2-Query to lookup queries behaving bad on Database and map them them to the right report (RDL) file. That way we can reach out to the right report owner. 

 

Apprecaiate the help, thank you!

Help with PBIRS ReportServer queries

2 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Abdelmajid 

     

    I'm afraid it's hard to achieve. After my search, I didn’t find any relevant methods that meets your needs.

     

    Best Regards

    Janey Guo

  • mpsrshl's avatar
    mpsrshl
    Advocate II
    5 years ago

    1: SELECT
    C.Name
    ,U.UserName
    ,R.RoleName
    --,R.Description
    ,U.AuthType
    ,c.ItemID
    ,C.Type
    ,LDAP.employeeID
    FROM Reportserver.dbo.Users U
    INNER JOIN Reportserver.dbo.PolicyUserRole PUR
    ON U.UserID = PUR.UserID
    INNER JOIN Reportserver.dbo.Policies P
    ON P.PolicyID = PUR.PolicyID
    INNER JOIN Reportserver.dbo.Roles R
    ON R.RoleID = PUR.RoleID
    INNER JOIN Reportserver.dbo.Catalog c
    ON C.PolicyID = P.PolicyID
    left join [yourLDAPuserbasetable] LDAP
    on replace(u.UserName,'[yourdomain]','') = sAMAccountName collate Latin1_General_100_CI_AS_KS_WS

     

    2:

    - Best advice is to switch SQL Profiler on.

    - Collate the data from SQL profiler with the SSRS logs.