Monday, February 9, 2015

SQL Server Reporting Services -- Report Permissions

One of my customers called me this morning with some questions regarding SSRS security. Specifically, he was having problems privileging his end users for reports he had created. He wasn't sure what to check, but this was the error the user was receiving:

  An error has occurred during report processing.
  Cannot create a connection to data source 'DATASOURCENAME'.
  For more information about this error navigate to the report   
  server on the local server machine, or enable remote errors

The user in question was able to launch Report Manager, but any attempt to open a report failed with the above message. After review, we confirmed the user was not privileged in the appropriate database. At this point, I explained the Role-based security model, and the different levels of SQL Server object privileges that are necessary. The problem was resolved when we enabled the correct database permissions for the user.

This post is just a quick query that you can use to see what roles and permissions are assigned to different report server objects.

     USE ReportServer;

     SELECT 
          u.UserName [User]
           r.RoleName [Role]
          r.Description [PrivilegeDescription]
          c.Path [ObjectPath]
          c.Name [ObjectType] 
     FROM
          dbo.PolicyUserRole pur INNER JOIN dbo.Policies p
             ON pur.PolicyID = p.PolicyID INNER JOIN dbo.Users u 
                ON pur.UserID = u.UserID INNER JOIN dbo.Roles r 
                  ON pur.RoleID = r.RoleID INNER JOIN dbo.Catalog c 
                     ON pur.PolicyID = c.PolicyID
     ORDER BY
          u.UserName   


This is an example of the output you will receive:

  User                 Role        PrivilegeDescription                                                  ObjectPath         ObjectType
  DOMAIN\login  Browser   May view folders, reports and subscribe to reports.    /Data Sources     Data Sources
  DOMAIN\login  Report Builder    May view report definitions                               /FolderName/rptname   Report


Please look here for more detail regarding the SSRS object security assignment for reports, report models and resources, and the report data sources.  

No comments:

Post a Comment