By: Scott Murray | Comments (1) | Related: > Reporting Services Report Builder
Problem
The security needed to install and run SQL Server Reporting Services Report Builder seems to have a hodgepodge of different permissions and rules. What are the different security permissions needed to install and run SQL Server Reporting Services Report Builder? Check out this tip to learn more.
Solution
SQL Server Reporting Services Report Builder security is a hodgepodge ( a very technical term ) of different areas of security settings. Many of the basic security items which relate to SQL Server Reporting Services, in general, apply to Report Builder as describe in this tip on SSRS Permissions. However, because of the unique nature of the Report Builder tool, several distinctive situations need to be taken into consideration when involving Report Builder in your reporting arsenal. In this tip we will discuss three areas of security consideration:
- Report Builder Installation
- Deployment and Execution of Report Builder Objects including reports, report parts, and data sources
- Report Model Security-this item will be discussed briefly as it has been deprecated
Furthermore, the overall security setup can be further complicated when Report Server is used in SharePoint integrated mode.
SQL Server Reporting Services Report Builder Installation
Report Builder 3.0 can be installed using two different methods. Either through the ClickOnce Technology ( http://msdn.microsoft.com/en-us/library/t71a733d.aspx ) which is available to install directly from your own Report Server website or the via a download of the actual installation media ( http://www.microsoft.com/en-us/download/details.aspx?id=29072 ), commonly known as a stand alone installation. With the stand alone installation, the process is similar to most other desktop installations ( "Next, Next, Next" ); however depending on the OS version, elevated permissions may be required to execute the installation and setup. A command line installation of the stand alone package further requires Administrator privileges. Many Administrators find the ClickOnce Method to be easier that the stand alone installation; however, specific permissions are required for the ClickOnce method. First, the ClickOnce method uses the Report Builder button available on the Ribbon of the Report Server site, as illustrated below.
Nevertheless, in order for the Report Builder button to appear, a user must have been granted specific permissions on the Report Server. It would seem logical that a Report Builder role permissions granted under the Folder Security area would toggle the appearance or lack thereof this button, as illustrated below.
However, that assumption is incorrect; in order for a user to see the Report Builder button, that user must be assigned to the system user role as illustrated in the next figure.
Alternately, as illustrated below, a custom system role can be created which has been assigned the Execute Report Definitions task; this customer role can also be assigned to the user in question. For additional details about setting up this custom system role, please see the Manage SQL Server Reporting Services in Management Studio tip.
You may be wondering, what the Report Builder Role does then. This role actually is used more for the building and consumption of reports as opposed to the installation of Report Builder. It basically allows report users to consume reports and to view and use Model definitions. We will discuss these functions in more detail within the next section. Fortunately, once the Report Builder button is enabled and a user clicks on it, the ClickOnce technology takes over and manages the installation process and permissions; this installation generally requires very little user interaction. After the initial install, any time the Report Builder button is clicked, the Report Builder application opens.
As an alternative to all the permissions settings noted above, Report Builder can also be installed manually using the file download noted in the introduction. The local Non-ClickOnce installation requires elevated Windows privileges when installing on Windows Vista or Windows 7. Otherwise the install should be easy to follow; see http://technet.microsoft.com/en-us/library/ff519551.aspx for details. Once we have Report Builder installed, we can move on to what permissions are needed to deploy and run Report Builder Objects.
Deployment and Execution of Report Builder Objects
After installing Report Builder, a report author will want to do a few different common tasks including downloading report definitions into Report Builder and actually run reports. Here is where the Report Builder role comes in usage. By default, this role provides access to the following tasks:
- Consume reports-Reads report definitions. This role allows a report designer to download a report from the report server and edit it in Report Builder.
- View reports-Run a report and view report properties.
- View resources-View resources and resource properties.
- View folders-View folder contents and navigate the folder hierarchy.
- View models-View models in the folder hierarchy, use models as data sources for a report, and run queries against the model to retrieve data.
- Manage individual subscriptions-Create, view, modify, and delete user-owned subscriptions to reports and linked reports, and create schedules in support of those subscriptions.
These task permissions are granted at the home or root level and traverse down into folder level (with propagation to folders below if custom security is not used for a sub folder). Furthermore these permissions allow the Report Builder designer to download a report locally into Report Builder for editing. In order to push any Report Builder reports back to the Report Server, two methods can be employed. Either the user can be assigned to the My Reports role, which will allow the user to save the newly designed report to a special My Reports folder which is available only to that user. To the contrary, the user can also be granted the Publisher role, which provides the ability to deploy Report Builder designed reports to the specific folder upon which the Publishing role was granted.
In order for a user to reuse objects such as a data source or report parts in a Report Builder report scenario, the user must have Report Builder permissions assigned to the folder where those objects exists, as shown below. Once the objects are included in a report, the user running the report must continue to have access to those items when the report is run.
Turning now to the actual running of a Report Builder report and moving past the tasks and roles assigned to a user, when running a report the back end database permissions are now authenticated and authorized for the actual database objects which are queried when running the report. That means that appropriate database level permissions must be granted to the user running the report. Thus, if stored procedures, views, and tables are used in the Report Builder report, appropriate execute and select permissions must be granted to the users running the reports. Two good tips to drill into these permissions include: http://www.mssqltips.com/sqlservertip/2171/implicit-permissions-due-to-ownership-chaining-or-scopes-in-sql-server/ and http://www.mssqltips.com/sqlservertip/1138/giving-and-removing-permissions-in-sql-server/. This scenario means that a user may have the appropriate access to the Report, Model, or Report Parts on the Report Server; however they may not have the appropriate permissions at the database level and thus receive an error when they attempt to run the report.
Report Model Security
When the first version of Report Builder was introduced, Report Builder Models
were touted as a way to deploy ready to use data models to end users. Behind the
scenes an Administrator would create a Model with all "joins" in place and fields
appropriately formatted or even hidden from the end user's view; the model, in some
ways, mimics a Data Source View created when designing a SSAS MultiDimensional databases
and cubes. These models made the process of creating a Report Builder report much
easier for many report designers. In SSRS 2012, models were depreciated and can
no longer be developed. However, you can still maintain your existing models from
previous versions. As such, models have multiple levels of permissions that
need to be considered. First, appropriate "view model" permissions (tasks)
must be granted to the folder where the models are stored on the Report Server as
discussed early on in this tip. Second, models allow for the adherence to Model
Item Security. Finally, models still persist to database level permission as discussed
in the final paragraph of the last section; i.e., you need the appropriate database
level access to the data in the tables and views. Since we already discussed the
first and third item, this section will focus, just briefly, on the second method.
Model Item Security allows an administrator to limit access to specific attributes
within the model. One method of maintaining the Model Item Security is through the
Report Server. To access Model Item Security, navigate to a model on the Report
Server as shown below; then highlight a model and right mouse click on the
right side of the title and click on the down arrow.
Next, select Manage from the options and last click on Model Item Security from the left side menu, clicking the "Secure individual model items independently for this model" check box then allows for the selection of individual model folders, attributes, and fields. Finally, users or groups can be assigned to the selected folder, attribute, or field, as shown below.
Once the Model Items Security is applied, only the user or group specifically assigned to that object will see that item when they open the model in Report Builder.
Conclusion
Report Builder is a wonderful end user reporting tool; however the security surrounding
installation and use of the tool is not always completely transparent. First,
appropriate SSRS system role permissions are needed for an end user to install the
tool from the Report Builder Button in SSRS; as an alternative, the Report Builder
tool can be downloaded and installed via a normal desktop installation process.
Once report Builder is installed, using the tool requires appropriate roles to download
and view various SSRS objects including reports, report parts, and data sources.
In order to deploy or publish the reports back to the report server, My Reports
or Publishing roles must be used. Furthermore, appropriate database level permissions
must be granted to the user running the report. Finally, Report Builder Models,
which are deprecated in SSRS 2012, adhere to similar database level and SSRS folder
level security. Additionally, Model Item Security can be applied to a Model to restrict
access to specific folders, attributes, and fields within a model.
Next Steps
- As you work with Report Builder be sure to understand the security implications.
- Report Builder Help for SQL 2012-http://technet.microsoft.com/en-us/library/hh965699.aspx
- Report Builder Resources on MSSQLTips.com:
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
