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?

Parents
  • 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]
  •  (or  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?

  •  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:

    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.

  •  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?

Reply
  •  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?

Children
No Data