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