In the previous part of this article, we introduced SQL Server Management Studio custom reports, showed their advantages over other native performance reporting features, and gave steps to create them. In this part, we will give some security recommendations and show how to run and modify these reports.

Warning

As any valid T-SQL statement can be executed through an rdl file, keep in mind that users who execute reports can access, modify, or delete other objects and records, if they have enough privileges.

A simple example of malicious code covered by a real report is an update or delete statement added after code that retrieves the performance metric information. If you add the following after code given earlier in this article, the report will be generated successfully, but the table records will be updated.

As you’re not notified about the statements and changes that occur when a custom report is run, you will probably not be aware of what has happened in the background.

This is also the reason why custom reports cannot be automatically executed from SQL Server Management Studio or the command line. Each time you want to run a report, you have to open it manually.

The recommended security measures are: deny custom report users the permission to modify the rdl files, grant them only the read permission on the report files and folders where they are stored, and set adequate permissions on database objects (e.g. deny them the permission to delete and update table records).

How to run a custom report

1. In SQL Server Management Studio, connect to the SQL Server instance you want to generate the report for. Note that even if the report is created for one SQL Server instance (e.g. Fujitsu\SQL2014 in this example), if you connect to a different instance (e.g. Fujitsu\SQL2012), the later instance will be queried and the report will contain its data, so you will get different reports, depending on the context used. In other words, you can use the same report on all your SQL Server databases and instances, as long as queried objects exist in them. If you want to avoid this behavior and execute a report only against a specific database, regardless the database connected to, add the USE statement at the beginning of the query
2. Right-click the Database node in Object Explorer

3. In the context menu, select Reports | Custom Reports

   

4. Navigate to the folder where you saved the project
5. Select the rdl file created in SQL Server Data Tools

6. If a warning about a potential threat appears, click Run

   

The report will be shown in a new Query tab

Once you run the report, it will appear in the list of the recently used reports (below the Custom Reports option), but only for that node. If you open the context menu for a different node in Object Explorer, the report will not be shown in the recent list.

By default, the recent list shows 10 last run reports. To modify this number, use the Items shown in the recently used lists option on the Tools | Options | Environment | General tab. Keep in mind that this option applies to all recently used lists in SQL Server Management Studio, not just to recently used custom reports.

If you try to run a custom report when connected to a SQL Server instance using a login that doesn’t have enough privileges to run the query to obtain the data shown in the report, you’ll get the following error message:

The user does not have permission to perform this action.

You will get the same error if you try to execute the T-SQL query used in the report in SQL Server Management Studio.

The same as standards reports, custom reports can be printed and exported into an Excel, PDF, or Word files.

How to modify a report

To make the changes using dialogs and wizards, use SQL Server Data Tools:

1. Start SQL Server Data Tools
2. To open the project where the rdl file was created, select File, then Open | Project/Solution in the menu

3. Navigate to the project and select the rptproj file

   

4. To modify the SQL Server instance, database, authentication type, and credentials used in the report, expand the Data Sources node in the Report Data pane. Right-click the item listed and select Data Source Properties, or select Edit in the Report Data pane menu
5. To modify the query used to obtain the data shown in the report, expand the Datasets node, right-click the data set used and select Query
6. To modify the report layout, use the Design pane. Adjusting the column size is needed quite often. To do that, just select and drag the column border, the same as in SQL Server Management Studio Results tab
7. The Report Data pane offers a range of built-in fields that can be dragged and dropped into the Design pane. These fields provide commonly requested information, such as the time when the report was generated, server name, report name, number of pages, etc.
8. If the stored procedure used as a query has parameters, you can modify the existing or add new ones using the Parameters node. Note that parameters used must have default values, otherwise an error will be thrown when you run a report

Another way to modify a custom report is to open the rdl in a text editor. This method requires some XML and rdl file structure knowledge, but it is not difficult to make basic changes.

For example, to modify the data sources used, go to the <DataSources> tag. The tag names and content are intuitive enough to enable easy modification:

To modify the query used, find the <CommandText> tag in the <Query> tag. You’ll find code generated in Query Builder there.

To modify the report layout, go through the first part of the file, and find the adequate tag, such as

Or

However, this is not a recommended method to modify a report.

As shown, custom reports can contain malicious code that can seriously harm your SQL Server. To prevent any potential damage, it’s necessary to give only minimal permissions to custom report users, and to protect custom report files from unauthorized changes. With these security measures in place, custom reports can do no harm.