Jeremiah Peschka

24 March 2020

• Redgate Monitor
• Database Performance Monitoring

Adding Reports Users to SQL Monitor

Custom reports and user roles

SQL Monitor provides a few built-in reports that can be sent to whoever needs them, on a schedule, but also allows users to create custom reports; after all, different users have different concerns about SQL Server.

Prior to SQL Monitor 10.0.5, a SQL Monitor administrator could assign users to one of two non-administrative user roles: Standard user or Read only user. A user in the Standard user role can create and edit reports, but this also means giving that user more privileges than are necessary. As well as creating and editing reports, members of Standard user can also make other (limited) configuration changes to SQL Monitor, such as configuring alert settings.

The only alternative was to assign the user to the Read only user role. Members of this role can view the state of certain servers, as specified by administrators, but can’t make any configuration changes. They can view existing reports, but they can’t edit them, and they can’t create their own reports. Notice that when logged in as a Read only user, the +New Report button is disabled, as is the ability to add a tile to an existing report:

If you needed a new report but were a member of the Read only user role, you’d have to search out a privileged user who can create the report for you. This isn’t a scalable approach; access to privileged users shouldn’t be a bottleneck to seeing the data that you need.

Fortunately, in SQL Monitor 10.0.5 and later, you have a better alternative: you can ask an administrator to assign you to the Reports user role.

The Reports user role

A member of the Reports user role can add, modify, and delete reports but has only read-only access to SQL Monitor beyond that. The only thing that Reports users can change are reports (and their own password); they cannot delete or change alerts, set thresholds, or perform any other administrative tasks.

This can be a useful role for many types of users. For example, think of a developer who works with several production servers; he or she may need to view the activity of those servers and create reports to diagnose issues on the servers, but should not be able to configure alerting or other performance thresholds.

This new role is available for installations using both Active Directory and SQL Monitor credentials – everybody can come to the party. However, with Active Directory authentication (which I’m using here) the administrator can also limit the access to individual servers and groups.

As an Administrator, head over to the Configuration page, and under Application options, click Manage users. You’ll see something like this, and you can add the user or group to the Reports user role:

Now switch to the test user that was added to the Reports user role, and now suddenly you can add tiles to the existing report or even create a new report!

Creating a custom report

Now that you can create a report, you might as well explore your newfound powers. Click +New report… and go crazy!

Step 1: An awesome name for the report

Since naming things is one of the hardest problems in computers, we’re already ahead of the curve.

Step 2: Adding things to the report

Our newly created report needs some tiles (or self-actualization):

Click on “Add Tile…” and you’ll see a fun little dialog that lets you add one of three types of tiles, including adding whatever analysis graphs you want. Here I’ve set up one with processor time, disk transfers/sec, and the buffer page life expectancy (I picked three things out of the lists; don’t do this at home, my report isn’t meaningful, just fun.)

And that’s all there is to it!

Summing Up

We created a Reports user, demonstrated how that user can add a report, and then viewed that report. Using these steps, you’ll be able to allow SQL Monitor users within your organization to self-serve custom reports, without having to them with more privileges than they need. This can also save time for administrators and other high privileged users.

If you’re curious about what you should be monitoring, check out Tony Davis’s Simple Talk article Eight Steps to Effective SQL Server Monitoring.