SQL Server Performance

Report on secruity for each report

Discussion in 'SQL Server 2005 Reporting Services' started by JonM80, Jul 21, 2008.

  1. JonM80 New Member

    Hi All,

    I'd like to create a report that gives me a list of all reports and which users have access to each of the reports. I currently use security groups through active directory but there are some instances where I need to specify specific users for sensitive reports.
    Has anyone every accomplished this? I can get a listing of all of the reports and descriptions by running this query against the ReportServer DB.

    select distinct Path + '/' + Name,[Description] from [catalog] CAT
    where Property not like '<Properties />' and Content is not NULL
    There is a Policies Table and PolicyUserRole table. However, I'm not sure on where to join to get the security levels for each. Any Thoughts?
    Thanks In advance for any help
  2. satya Moderator

  3. JonM80 New Member

    Where can I find an ERD for Report Server?
  4. JonM80 New Member

    Hi satya,
    Thanks for link, but that was all general info I was looking for specific table info for the ReportServerDb. Anyways I figured out how I could achieve this, here it is. Just took a little bit of playing around with tables. My main objective here was to create a catalog of reports and show which users have access to each. I'm going to throw this into a matrix object and I should be all set.
    select Path + '/' + Name as [Report],[Description],USr.UserName
    from [catalog] CAT
    inner join
    PolicyUserRole POLR on POLR.PolicyID = CAT.PolicyID
    inner join
    Users USR on USR.UserID = POLR.UserID
    where Property not like '<Properties />' and Content is not NULL
    order by [Report]

    Thanks Again,

Share This Page