Sunday, July 10, 2011

SSRS - Reportserver Database Tables Explored - Part 3

Today let us take look into the security related below tables

1. Users
2. Policies
3. Roles
4. PolicyRole
5. CachePolicy
6. SecData
7. ModelItemPolicy
 
Users

Stores the list of users

Some important fields
Usertype - Stores User type
                 0 – system defined
                 1 – User Defined

Policies

Stores a one policy per userdefined object created. If policy flag is 1 , that is system defined policy for the system administrators.

The policyId created will be mapped to each userdefined object in catalog table.

Roles

Stores the Roles related to the Reports. Presently available roles are

BrowserMay view folders, reports and subscribe to reports.
Content ManagerMay manage content in the Report Server. This includes folders, reports and resources.
Model Item Browser Allows users to view model items in a particular model.
My ReportsMay publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.
PublisherMay publish reports and linked reports to the Report Server.
Report
BuilderMay view report definitions.
System Administrator View and modify system role assignments, system role definitions, system properties,and shared schedules.
System UserView system properties, shared schedules, and allow use of Report Builder or other clients that execute report definitions.

PolicyRole

This stores the mapping between the User , Role and policy of a catalog object.

CachePolicy

Stores the cache details in processing options section of reports. If you select donot cache option no records will be inserted in this table.

SecData

Stores the total permission for the reports and other objects in XML format .

ModelItemPolicy

This table stores the data of the access provided at the modelitem level in ModelItemSecurity tab under the Manage model.

Sample usage queries

1. Get the users and their roles mapped for the reports

SELECT c.name,
       u.username,
       u.authtype,
       r.rolename,
       r.DESCRIPTION
 FROM users u
 JOIN policyuserrole pur
   ON u.userid = pur.userid
 JOIN policies p
   ON p.policyid = pur.policyid
 JOIN roles r
   ON r.roleid = pur.roleid
 JOIN catalog c
   ON c.policyid = p.policyid
WHERE c.TYPE = 2 -- For Reports Only
ORDER BY name,username 


2. Get the Cache Policy for the Reports

SELECT c.name,
       cp.cacheexpiration,
       cp.expirationflags
 FROM  cachepolicy cp
 JOIN  catalog c
   ON  c.itemid = cp.reportid

3. Get the Security Details XML from SecData Table


 SELECT c.name,
       CONVERT(XML, sec.xmldescription)
  FROM catalog c
  JOIN secdata sec
    ON c.policyid = sec.policyid
 WHERE c.TYPE = 2 


4. Get the Model Item's User and Role

SELECT c.name,
       mip.modelitemid,
       u.username,
       r.rolename
  FROM catalog c
  JOIN modelitempolicy mip
    ON c.itemid = mip.catalogitemid
  JOIN policies p
    ON p.policyid = mip.policyid
  JOIN policyuserrole pur
    ON p.policyid = pur.policyid
  JOIN users u
    ON u.userid = pur.userid
  JOIN roles r
    ON r.roleid = pur.roleid 

1 comment:

  1. Thank you for sharing this information! I can user Catalog, Policy, UserPolicyRole and Users table to get list of reports having rights to specific user. However, I am not able to find how to get list of reports for users who are part of "BUILTIN\Administrator" group.

    ReplyDelete