One day we found that we need to check users and groups permissions on SQL Reporting Services to understand who has access to what. So the next question is how to get all permissions in SQL Reporting Services programmatically and possibly export them to Excel for future analysis. After some time searching I ended up with the following SQL query:
|
1 2 3 4 5 6 7 |
use reportserver; select U.UserName, R.RoleName,R.Description, C.Path, C.Name from dbo.PolicyUserRole A inner join dbo.Policies P on A.PolicyID = P.PolicyID inner join dbo.Users U on A.UserID = U.UserID inner join dbo.Roles R on A.RoleID = R.RoleID inner join dbo.Catalog C on A.PolicyID = C.PolicyID |
This query returned all SQL Reporting Services users and their respective permissions.
If you need permissions list for particular user you can use the following query:
|
1 2 3 4 5 6 7 8 |
use reportserver; select U.UserName, R.RoleName,R.Description, C.Path, C.Name from dbo.PolicyUserRole A inner join dbo.Policies P on A.PolicyID = P.PolicyID inner join dbo.Users U on A.UserID = U.UserID inner join dbo.Roles R on A.RoleID = R.RoleID inner join dbo.Catalog C on A.PolicyID = C.PolicyID WHERE UserName like '%admin%' |
Now simple combinations like Ctrl+A, Ctrl+C and Ctrl+V in the Excel spreadsheet and you have all the information as convenient table!
Good luck!
