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

Browser	May view folders, reports and subscribe to reports.
Content Manager	May 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 Reports	May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.
Publisher	May publish reports and linked reports to the Report Server. Report
Builder	May view report definitions.
System Administrator	View and modify system role assignments, system role definitions, system properties,and shared schedules.
System User	View 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