3

What is the purpose of the [dbo].][Policies] table in SSRS ReportServerDB database?

I see two columns, PolicyID and PolicyFlag and I don't get any clue about purpose of this table. Is it possible to add anew row into that table and what represents those data, i.e. rows?

veljasije
  • 6,722
  • 12
  • 48
  • 79

1 Answers1

3

Each row of Policies table declares via PolicyFlag column if that policy is defined for system roles.

A policy can be related only with created user defined objects, UserType = 1 in User table. System defined objects like NT AUTHORITY\SYSTEM and Local Server Administrator aren't mapped with policies.

If you run this query:

SELECT b.username, 
       c.policyflag, 
       d.rolename, 
       b.usertype, 
       c.policyid 
FROM   policyuserrole a 
       INNER JOIN users b 
               ON a.userid = b.userid 
       INNER JOIN policies c 
               ON a.policyid = c.policyid 
       INNER JOIN roles d 
               ON a.roleid = d.roleid

enter image description here

By default you will have two rows in Policies table.

REFERENCE

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • 2
    Worth noting that, even after knowing this info (the relationship between these 3 tables), you still cannot manually add user-role mappings via TSQL, even if you get the SID's correct. You must do it via Report Manager. :\ – NateJ Oct 15 '18 at 15:23