Snippet Name: SSRS_Folder_Permissions_Query

Tags:

Created Date: 2025-06-16 13:12:49
Last Modified Date: 2025-05-31 10:31:41

Dependencies:

DBA Notes:

Sample Output:

Title: SSRS Folder Permissions Query
Author: user
iN SQL Server Reporting Server, can you help find permissions that are applied on a folder or subfolder basis

Author: ChatGPT
In SQL Server Reporting Services (SSRS), permissions are managed at various levels including the folder and subfolder levels. To find the permissions applied to a folder or subfolder, you can use the Reporting Services web service or the SSRS database. Here???s how you can do it:

### Using the Report Server Web Service
You can use the ReportService2010 endpoint to retrieve permissions. Below is an example of how you might do this using PowerShell:

```powershell
# Define the web service URL and the folder path
$reportServerUri = "http://YourReportServer/ReportServer/ReportService2010.asmx"
$folderPath = "/YourFolder"

# Load the Reporting Services Web Service
$reportService = New-WebServiceProxy -Uri $reportServerUri -Namespace SSRS -UseDefaultCredential

# Get the policies applied to the folder
$policies = $reportService.GetPolicies($folderPath)

# Display the policies
foreach ($policy in $policies) {
    Write-Output "Group/User: $($policy.GroupUserName)"
    foreach ($role in $policy.Roles) {
        Write-Output "  Role: $($role.Name)"
    }
}
```

### Using the ReportServer Database
The ReportServer database stores information about items, including folders and their permissions. You can query this database to find the permissions. Here's an example SQL query to retrieve permissions:

```sql
SELECT 
    f.Path AS FolderPath,
    u.UserName,
    r.RoleName
FROM 
    Catalog f
JOIN 
    PolicyUserRole pur ON f.PolicyID = pur.PolicyID
JOIN 
    Users u ON pur.UserID = u.UserID
JOIN 
    Roles r ON pur.RoleID = r.RoleID
WHERE 
    f.Type = 1 -- Type 1 represents folders
    AND f.Path LIKE '/YourFolderPath/%' -- Specify your folder path
ORDER BY 
    f.Path, u.UserName;
```

### Steps to Execute the Query:

1. **Connect to the ReportServer Database:**
   Use SQL Server Management Studio (SSMS) to connect to your ReportServer database.

2. **Run the Query:**
   Execute the above query to get a list of users and their roles on folders and subfolders within the specified path.

By using either of these methods, you can effectively retrieve the permissions applied to folders and subfolders in SSRS.

Author: user
i would like all folders, and not any specific folder isntead

Author: ChatGPT
To retrieve permissions for all folders and subfolders in SQL Server Reporting Services (SSRS), you can adjust the SQL query to remove the specific folder path condition. Here???s how you can do it:

### Using the ReportServer Database

You can query the ReportServer database to retrieve permissions for all folders and subfolders. Here's an updated SQL query:

```sql
SELECT 
    f.Path AS FolderPath,
    u.UserName,
    r.RoleName
FROM 
    Catalog f
JOIN 
    PolicyUserRole pur ON f.PolicyID = pur.PolicyID
JOIN 
    Users u ON pur.UserID = u.UserID
JOIN 
    Roles r ON pur.RoleID = r.RoleID
WHERE 
    f.Type = 1 -- Type 1 represents folders
ORDER BY 
    f.Path, u.UserName;
```

### Steps to Execute the Query:

1. **Connect to the ReportServer Database:**
   Use SQL Server Management Studio (SSMS) to connect to your ReportServer database.

2. **Run the Query:**
   Execute the above query to get a list of users and their roles on all folders and subfolders.

This query will list all the folders and subfolders along with the users and their associated roles.

Author: user
it looks like the permission in subfolders are cascaded from the parent folder. 

Author: ChatGPT
In SQL Server Reporting Services (SSRS), permissions can indeed cascade from parent folders to subfolders, meaning that subfolders inherit permissions from their parent folders unless explicitly overridden. 

To get a complete and accurate list of permissions applied to all folders and subfolders, including inherited permissions, you'll need to handle the inheritance explicitly in your query or script.

### Detailed Steps

1. **Identify Inherited Permissions:**
   Permissions can be inherited or explicitly set. The `InheritanceFlag` in the `PolicyUserRole` table indicates whether a policy is inherited.

2. **Retrieve All Permissions Including Inherited Ones:**
   You need to account for both inherited and explicitly set permissions. Here is a modified SQL query to capture this:

### SQL Query

```sql
WITH FolderPermissions AS (
    SELECT 
        f.Path AS FolderPath,
        u.UserName,
        r.RoleName,
        CASE 
            WHEN pur.InheritanceFlag = 1 THEN 'Inherited'
            ELSE 'Explicit'
        END AS PermissionType
    FROM 
        Catalog f
    JOIN 
        PolicyUserRole pur ON f.PolicyID = pur.PolicyID
    JOIN 
        Users u ON pur.UserID = u.UserID
    JOIN 
        Roles r ON pur.RoleID = r.RoleID
    WHERE 
        f.Type = 1 -- Type 1 represents folders
)
SELECT * FROM FolderPermissions
ORDER BY FolderPath, UserName;
```

### Explanation:

1. **Common Table Expression (CTE):** 
   The `FolderPermissions` CTE fetches the path, user name, role name, and whether the permission is inherited or explicit.

2. **InheritanceFlag:** 
   The `InheritanceFlag` is used to determine if the permission is inherited. `InheritanceFlag = 1` indicates the permission is inherited.

3. **Ordering:** 
   The final SELECT statement orders the results by folder path and user name for better readability.

### Steps to Execute the Query:

1. **Connect to the ReportServer Database:** 
   Use SQL Server Management Studio (SSMS) to connect to your ReportServer database.

2. **Run the Query:** 
   Execute the above query to get a list of users and their roles on all folders and subfolders, including whether the permissions are inherited or explicitly set.

This query will provide a comprehensive list of permissions, distinguishing between inherited and explicitly set permissions.

Author: user
I don't see any column named InheritanceFlag in any table, including PolicyUserRole

Author: ChatGPT
You are right. SSRS does not have an `InheritanceFlag` column. The inheritance is managed by the `PolicyID` which is shared among the items (folders, reports, etc.) that inherit the same security settings.

To properly account for inherited permissions, you need to recursively check the `PolicyID` from parent folders down to subfolders. Here's a more detailed approach:

### SQL Query for SSRS Folder Permissions Including Inherited Permissions

```sql
WITH RecursiveFolders AS (
    SELECT 
        c.ItemID,
        c.Path,
        c.Name,
        c.ParentID,
        c.PolicyID,
        CAST(c.Path AS NVARCHAR(MAX)) AS FullPath
    FROM 
        Catalog c
    WHERE 
        c.Type = 1 -- Type 1 represents folders
    
    UNION ALL

    SELECT 
        c.ItemID,
        c.Path,
        c.Name,
        c.ParentID,
        p.PolicyID,
        CAST(rf.FullPath + '/' + c.Name AS NVARCHAR(MAX)) AS FullPath
    FROM 
        Catalog c
    JOIN 
        RecursiveFolders rf ON c.ParentID = rf.ItemID
    JOIN 
        Policies p ON p.PolicyID = rf.PolicyID
    WHERE 
        c.Type = 1
)
SELECT 
    rf.FullPath AS FolderPath,
    u.UserName,
    r.RoleName
FROM 
    RecursiveFolders rf
JOIN 
    PolicyUserRole pur ON rf.PolicyID = pur.PolicyID
JOIN 
    Users u ON pur.UserID = u.UserID
JOIN 
    Roles r ON pur.RoleID = r.RoleID
ORDER BY 
    rf.FullPath, u.UserName;
```

### Explanation:

1. **Recursive Common Table Expression (CTE):** 
   - The `RecursiveFolders` CTE recursively collects all folders and their paths, starting from the root folders.
   - It assigns the `PolicyID` of the parent folder to the subfolders to reflect inheritance.

2. **Full Path Calculation:** 
   - The `FullPath` is built recursively to represent the complete path of each folder.

3. **Joining with PolicyUserRole:** 
   - The `RecursiveFolders` CTE is joined with the `PolicyUserRole`, `Users`, and `Roles` tables to retrieve the permissions.

### Steps to Execute the Query:

1. **Connect to the ReportServer Database:** 
   Use SQL Server Management Studio (SSMS) to connect to your ReportServer database.

2. **Run the Query:** 
   Execute the above query to get a list of users and their roles on all folders and subfolders, including inherited permissions.

This query will provide a comprehensive view of the permissions applied to all folders and subfolders in SSRS, reflecting both explicitly set and inherited permissions.

Author: user
can you change the code to concatenate the RoleName, so that if the userName had, say four roles, like Browser,Content Manager,My Reports AND Publisher, it's a single cell instead of four rows

Author: ChatGPT
Sure, you can use the `STRING_AGG` function (available in SQL Server 2017 and later) to concatenate the roles into a single cell. Here's how you can modify the query to achieve that:

### SQL Query with Concatenated Roles

```sql
WITH RecursiveFolders AS (
    SELECT 
        c.ItemID,
        c.Path,
        c.Name,
        c.ParentID,
        c.PolicyID,
        CAST(c.Path AS NVARCHAR(MAX)) AS FullPath
    FROM 
        Catalog c
    WHERE 
        c.Type = 1 -- Type 1 represents folders
    
    UNION ALL

    SELECT 
        c.ItemID,
        c.Path,
        c.Name,
        c.ParentID,
        p.PolicyID,
        CAST(rf.FullPath + '/' + c.Name AS NVARCHAR(MAX)) AS FullPath
    FROM 
        Catalog c
    JOIN 
        RecursiveFolders rf ON c.ParentID = rf.ItemID
    JOIN 
        Policies p ON p.PolicyID = rf.PolicyID
    WHERE 
        c.Type = 1
)
SELECT 
    rf.FullPath AS FolderPath,
    u.UserName,
    STRING_AGG(r.RoleName, ', ') AS Roles
FROM 
    RecursiveFolders rf
JOIN 
    PolicyUserRole pur ON rf.PolicyID = pur.PolicyID
JOIN 
    Users u ON pur.UserID = u.UserID
JOIN 
    Roles r ON pur.RoleID = r.RoleID
GROUP BY 
    rf.FullPath, u.UserName
ORDER BY 
    rf.FullPath, u.UserName;
```

### Explanation:

1. **Recursive Common Table Expression (CTE):**
   - The `RecursiveFolders` CTE recursively collects all folders and their paths, starting from the root folders.
   - It assigns the `PolicyID` of the parent folder to the subfolders to reflect inheritance.

2. **Full Path Calculation:**
   - The `FullPath` is built recursively to represent the complete path of each folder.

3. **Concatenate Roles:**
   - The `STRING_AGG` function is used to concatenate the roles into a single cell, separated by commas.

4. **Group By:**
   - The results are grouped by `FolderPath` and `UserName` to ensure roles are concatenated for each user in each folder.

### Steps to Execute the Query:

1. **Connect to the ReportServer Database:**
   Use SQL Server Management Studio (SSMS) to connect to your ReportServer database.

2. **Run the Query:**
   Execute the above query to get a list of users and their concatenated roles on all folders and subfolders.

This query will provide a comprehensive view of the permissions applied to all folders and subfolders in SSRS, with roles concatenated into a single cell for each user.

Author: user
my instnace is not SLQ2017; can you do the same using the FOR XML concatenation technique instead

Author: ChatGPT
Certainly! You can use the `FOR XML PATH` method to concatenate roles into a single cell in versions of SQL Server prior to 2017. Here's how you can modify the query to achieve that:

### SQL Query with Concatenated Roles Using `FOR XML PATH`

```sql
WITH RecursiveFolders AS (
    SELECT 
        c.ItemID,
        c.Path,
        c.Name,
        c.ParentID,
        c.PolicyID,
        CAST(c.Path AS NVARCHAR(MAX)) AS FullPath
    FROM 
        Catalog c
    WHERE 
        c.Type = 1 -- Type 1 represents folders
    
    UNION ALL

    SELECT 
        c.ItemID,
        c.Path,
        c.Name,
        c.ParentID,
        rf.PolicyID,
        CAST(rf.FullPath + '/' + c.Name AS NVARCHAR(MAX)) AS FullPath
    FROM 
        Catalog c
    JOIN 
        RecursiveFolders rf ON c.ParentID = rf.ItemID
    WHERE 
        c.Type = 1
)
SELECT 
    rf.FullPath AS FolderPath,
    u.UserName,
    STUFF((
        SELECT ', ' + r.RoleName
        FROM PolicyUserRole pur
        JOIN Roles r ON pur.RoleID = r.RoleID
        WHERE pur.PolicyID = rf.PolicyID AND pur.UserID = u.UserID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Roles
FROM 
    RecursiveFolders rf
JOIN 
    PolicyUserRole pur ON rf.PolicyID = pur.PolicyID
JOIN 
    Users u ON pur.UserID = u.UserID
GROUP BY 
    rf.FullPath, u.UserName
ORDER BY 
    rf.FullPath, u.UserName;
```

### Explanation:

1. **Recursive Common Table Expression (CTE):**
   - The `RecursiveFolders` CTE recursively collects all folders and their paths, starting from the root folders.
   - It assigns the `PolicyID` of the parent folder to the subfolders to reflect inheritance.

2. **Full Path Calculation:**
   - The `FullPath` is built recursively to represent the complete path of each folder.

3. **Concatenate Roles:**
   - The `STUFF` function combined with `FOR XML PATH('')` is used to concatenate the roles into a single cell, separated by commas.

4. **Group By:**
   - The results are grouped by `FolderPath` and `UserName` to ensure roles are concatenated for each user in each folder.

### Steps to Execute the Query:

1. **Connect to the ReportServer Database:**
   Use SQL Server Management Studio (SSMS) to connect to your ReportServer database.

2. **Run the Query:**
   Execute the above query to get a list of users and their concatenated roles on all folders and subfolders.

This query will provide a comprehensive view of the permissions applied to all folders and subfolders in SSRS, with roles concatenated into a single cell for each user using the `FOR XML PATH` technique.