Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Removing sysadmin from monitoring account

The support document linked below has to do with removing sysadmin privileges from the DPA monitoring user account on a SQL Server instance.

https://support.solarwinds.com/Success_Center/Database_Performance_Analyzer_(DPA)/Knowledgebase_Articles/SQL_Server_permissions_for_DPA_monitoring

We are evaluating v12 of DPA and the permissions script in the support doc contains the following comment:

--Only run the following commands if the system option

--SQL_SERVER_WMI_METRICS_ENABLED is set to True:

I’m having a little bit of trouble trying to find information regarding how to check if the specified wmi system option is set or not. Can someone assist here?

Find more posts tagged with

Accepted answers

darichar

To see that setting within DPA, click on Options and click into the Administration tab. Click on Advanced Options button and within the System options tab check the Support Options box. Search for WMI and you should see the setting. This should be set to false by default if you are running DPA 10.x and higher.

All comments

darichar

FormerMember

Much obliged !

darichar

We are currently updating this KB article, and found that an extra step may be required for some of the new DPA 12 features. Within the GUI, when you drill into Table Tuning (new Tuning tab), the page queries information about the table and can fail after the sysadmin priv is removed. If you experience that issue here is the script that has worked for several customers. Let me know how it works for you.

DECLARE @DPA_User varchar(50) = 'dpa_m'; -- if AD account, set this to DOMAIN\user, e.g. TUL\dpa_m

DECLARE @dbname VARCHAR(50);

DECLARE @SQL NVARCHAR(max);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR

SELECT name

FROM MASTER.dbo.sysdatabases;

OPEN dbs;

FETCH NEXT FROM dbs INTO @dbname;

@FETCH_STATUS = 0

BEGIN

SET @SQL = 'use '+@dbname +';

CREATE USER '+@DPA_User+'; FOR LOGIN '+@DPA_User+';;

EXECUTE sp_addrolemember N''db_datareader'', '+@DPA_User+';;'

EXECUTE sp_executesql @SQL;

FETCH NEXT FROM dbs INTO @dbname;

END;

CLOSE dbs;

DEALLOCATE dbs;

darichar

Sorry, more about this script. Modify the first line and substitute your DPA monitoring username where it has 'dpa_m'. This script adds that user to each of the databases within the instance and gives it db_datareader privilege to get information about tables.

FormerMember

I spent some more time trying to get this to work for a 2012 instance but was unsuccessful. After the monitoring login is removed from sysadmin and added to 'MonitorRole' (below), the 'Table Tuning Advisor' form stops working properly. It is unable to display the 'Current Table Information:' area in the bottom half of the form and says 'You are not authorized to perform this action. Contact your DPA administrator.'

I'll likely come back to this at some point since I'm trying to avoid the 'add user and db_datareader role membership to all databases' workaround. My last test script is included below - can anyone see any permissions that I neglected to include? We are running DPA 12.0.3074 - the application server is running WS 2016, database server is running SS 2017 on WS 2016, and the monitored instance is SS 2012 SP4+ on WS 2012 R2.

Thanks for your time and advice!

CREATE LOGIN [MonitorTesting]
    WITH PASSWORD = N'BadPassword', DEFAULT_DATABASE = [master],
    CHECK_EXPIRATION = ON, CHECK_POLICY = ON

USE [master]
CREATE SERVER ROLE [MonitorRole] AUTHORIZATION [sa]
ALTER SERVER ROLE [MonitorRole] ADD MEMBER [MonitorTesting]

GRANT VIEW ANY DATABASE TO [MonitorRole]
GRANT VIEW SERVER STATE TO [MonitorRole]
GRANT VIEW ANY DEFINITION TO [MonitorRole]
GRANT CONNECT SQL TO [MonitorRole]
GRANT ALTER ANY EVENT SESSION TO [MonitorRole]

madisonpaul

@darichar (or @[Deleted User] or anyone else) any updates on this, esp. wrt. DPA 2020.2? I hope a better way has been found for SQL Server 2012 instances. The support articles I found still have the same basic suggestion. Just seems odd to recommend a method that obviously becomes stale as soon as you add a new database to the instance.

If the requirement is still there to touch every database in a SQL 2012 instance, has anyone considered adding the user to the MODEL DB, so newly created DBs inherit it? Would that resolve the future-proofing issue?

**EDIT** I just noticed that DPA 2021.1 is out (yeah, has been for a while...), and one of the claimed features is the ability to monitor without sysadmin privileges. So does that basically build the above exercise directly into the product? Does that mean that DPA 2021.1 can allow you to monitor a SQL Server 2012 instance without sysadmin, and without touching every database as described above?

cpriddy

@madisonpaul As of DPA 2020.2.1 and later, you can register a self-managed SQL Server instance without a privileged user. Basically you self-create a monitoring user, manually grant permissions to it, and then use the DPA mass registration wizard.

Until SQL Server 2014, you don't have the ability to "GRANT VIEW ANY DATABASE TO [dpa_mon_user]". I haven't tried adding the user to the Model database, but that's a great idea and don't see why it wouldn't work.

Refer to the "Register a SQL Server database instance" section in the online DPA Administrator Guide as well as the support KB Article "Register a SQL Server instance for DPA monitoring without the sysadmin role" for details.

If you have already registered a SQL Server and want to remove the sysadmin role, see this article "After registering a SQL Server instance, remove the SYSADMIN role from the DPA monitoring user"

Refer to the "What We're Working On for DPA" Thwack page for any related registration enhancements.

Related support articles for monitoring other DB types without a privileged user:

Register an Amazon RDS SQL Server instance for DPA monitoring without the sysadmin role
Register an Oracle database instance for DPA monitoring with reduced permissions
Register an Amazon RDS Oracle database instance for DPA monitoring with reduced permissions
Register a Sybase database instance for DPA monitoring without the privileged user
The registration wizards for Azure SQL DB, MySQL, PostgreSQL, Percona, and MariaDB database instances already allow you to specify an existing account. Follow the registration instructions for each in the DPA Administrator Guide matching your installed version of the DPA Server.

Note: For some database types, reducing certain privileges for the monitoring user may negatively affect certain DPA features. Make sure to read the DB registration section in the DPA Administrator Guide and any related articles to which it refers to identify any potential feature impacts.

madisonpaul

@cpriddy Thanks, I guess that answers one question. I had already read the above-mentioned KB articles, as they were obviously the inspiration for this thread. Since they both contain the CURSOR code for looping through the existing databases, I guess that means that the requirement to touch each database in SQL 2012 remains. As does the future-proofing issue.

So that leaves my other idea/question - what about adding the monitoring user to the MODEL database, to resolve the issue of databases created in the future? Has anyone actually TRIED it and tested the results?

Another thought - rather than adding the DB_DATAREADER role to the monitoring user on each DB, what about doing a GRANT CONNECT on each DB? So replace the code inside the cursor loop above, with something like:

   SET @SQL = 'use '+@dbname +';
       CREATE USER '+@DPA_User+'; FOR LOGIN '+@DPA_User+';;
         GRANT CONNECT TO '+@DPA_User+';;'

Would that be enough to give DPA on SQL Server 2012 the rights to do.... whatever it does at the database level?