SQL Server SSRS - Determine report permissions via ReportServer database tables?

jtoj6r0c  于 2023-10-15  发布在  其他
关注(0)|答案(3)|浏览(133)

How can one programmatically determine logins/users that have permission to access specific SSRS reports?

I want to create a datamart in order to populate reports for managers who want to see who has access to specific reports. We currently assign permissions to Active Directory groups which are then used by SQL Server and SSRS to determine permissions. I want to know if there is a table within SSRS's metadata which tracks how permissions are assigned to reports.

wa7juj8i

wa7juj8i1#

This is a script that does most of what you want, you can tweak it to your needs:

select C.UserName, D.RoleName, D.Description, E.Path, E.Name 
from dbo.PolicyUserRole A
   inner join dbo.Policies B on A.PolicyID = B.PolicyID
   inner join dbo.Users C on A.UserID = C.UserID
   inner join dbo.Roles D on A.RoleID = D.RoleID
   inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName

you can run the script on the SSRS SQL ReportServer

zz2j4svz

zz2j4svz2#

The above script will work but keep in mind that it will also display deleted users - i.e. users that are no longer in Active Directory which may be confusing sometimes.

Also, Microsoft does not officially support any queries against their ReportServer database.

xlpyo6sf

xlpyo6sf3#

Here is a query I came up with which only shows the unique permissions (root folder and wherever folder security inheritance is broken) instead of listing out every report catalog item. This layout is easier to read for things like access reviews if you have a lot of reports. Tested with SQL 2014.

Note: as mentioned by Paul this will show what is assigned in SSRS but isn't aware of if an Active Directory account is disabled or has been deleted.

select 
     [Path] = 
        case 
        when r.RoleFlags = 1 and c.Path is null
        then N'-Site Settings-' 
        else 
            case 
            when c.Path = N'' 
            then N'/' 
            else c.Path 
            end 
        end
    , MemberName = u.UserName
    , r.RoleName
    , RoleDescription = r.Description
    --, pur.PolicyID
    , RoleType = --Roles.RoleFlags values are from SecurityScope enum. Used to interpret Roles.TaskMask field.
        case r.RoleFlags
        when 0 then 'Folder Security'
        when 1 then '-Site Settings-'
        when 2 then 'Model Item'
        else concat('Unknown: ', r.RoleFlags)
        end
    , ReportServerName = @@SERVERNAME
    , ExecTime = getdate()
    --, r.RoleFlags
from ReportServer.dbo.PolicyUserRole pur
   inner join ReportServer.dbo.Users u on pur.UserID = u.UserID
   inner join ReportServer.dbo.Roles r on pur.RoleID = r.RoleID
   --get the root catalog item for each policy
   outer apply (
        select top 1 c.Path, c.Name
        from ReportServer.dbo.Catalog c 
        where pur.PolicyID = c.PolicyID 
            and r.RoleFlags = 0 --Catalog items
        order by len(c.path) - len(replace(c.path, N'/', N'')) --get the highest level items where this policy is applied.  Children that inherit from here will have more "/"s than the parent.
            , c.path
    ) c
order by 
    RoleType,
    c.Path,
    u.UserName,
    r.RoleName
;

相关问题