Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I would like to get the list of catalog items for a particular windows logged in user (Domain\username) from Report Server database using the SSRS REST API call in C#.NET.
Access to the reports are given using the windows Active Directory groups to the users in SSRS. Something like the below query, by making a REST API call from C# application.

SELECT *
FROM Catalog 
INNER JOIN Policies ON Catalog.PolicyID = Policies.PolicyID 
INNER JOIN PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID 
INNER JOIN Users ON PolicyUserRole.UserID = Users.UserID
INNER JOIN Roles ON Roles.RoleID = PolicyUserRole.RoleID 
WHERE Users.Username like '%DomainName\username' AND HIDDEN = 0


What I have tried:

Currently i am trying to make the below REST API call to get all the catalog Items

http://localhost/reports/api/v2.0/CatalogItems[^]

And then looping through each catalog Item to make the below api call to check whether the user has got access to that catalog Item
http://localhost/reports/api/v2.0/CatalogItems({Id})/Policies[^]

If there are 100 catalog Items then I am looping 100 times to make 100 api calls to check if the user has got access to that catalog item.

My question is, is there any api call which directly gives me the list of catalog items associated to the given windows user (Domain\username)? or is there any better way of doing it?
Posted
Updated 3-Dec-21 8:54am

I have never had to deal with SQL reporting, but...

0) There doesn't appear to be a Rest API that provides that functionality.

1) You can use ADO.Net to submit that query to the report server.

2) I suggest changing the query to this, so you can verify that the found user name is indeed the specified user name:

3) Third, you should use a parameterized query to avoid SQL injection.

SQL
SELECT u.Username, c.*
FROM  Catalog             AS c
INNER JOIN Policies       AS p   ON p.PolicyID   = c.PolicyID
INNER JOIN PolicyUserRole AS pur ON pur.PolicyID = c.PolicyID 
INNER JOIN Users          AS u   ON u.UserID     = pur.UserID
INNER JOIN Roles          AS r   ON r.RoleID     = pur.RoleID 
WHERE u.Username LIKE '%'+@username -- (@username includes the domain)
AND   HIDDEN = 0


Again, I don't know anything about report server, so YMMV.
 
Share this answer
 
#realJSOP
Thanks for your answer. As a work around currently I am doing the ADO.NET call as you described above, but I am looking to achieve this using the REST API call rather than ADO.NET call or SOAP API call.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900