24 March 2020
24 March 2020

Adding Reports Users to SQL Monitor

SQL Monitor 10.0.5 now supports a user role called "Reports user". A user added to this role, by an administrator, will be able to 'self-serve' SQL Monitor reports but won't be able to configure any other aspect of the monitoring service. Jeremiah Peschka explains how it works.

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.

 

You may also like

  • Event

    SEACON 2018 (The Study of Enterprise Agility Conference)

    SEACON is THE Enterprise Agility conference that brings business and technology together, and Redgate are pleased to be taking part as one of the sponsors. Following the sold out conference in 2017, the 2018 edition will again host FinTech practitioners and Thought Leaders in Enterprise Transformation, Entrepreneurial Leadership, Agile , DevOps, Cloud and Fintech.

  • Article

    Checking for Database Events Using Extended Events and SQL Monitor

    You need to make sure that nobody tampers with your production databases, or really any databases outside Development. Even if you weren't of a nervous disposition, you'd want to know if a database was stopped or removed. You'd also be intrigued by the sudden unrehearsed addition of a database to a production server.

  • Webinar

    Extending DevOps to the database: continuous integration

    Are you ready to take the next steps in your Database DevOps journey to see how to automate database deployment alongside your application code?

  • Event

    WinOps London 2018

    The world’s only dedicated conference to ‘DevOps in a Windows World’. The conference is about discovering and sharing experiences of using products and tools within the Microsoft DevOps world such as: PowerShell, TeamCity, Octopus Deploy, Azure, Vagrant, Chocolatey, AppDynamics, ScriptRock, Chef, Puppet, Ansible, Docker etc… Register for the event and meet the Redgate team.

  • Forums

    SQL Monitor Forum

    Real-time SQL Server performance monitoring, with alerts and diagnostics