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?

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

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

    WHILE @@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;

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

  • darichar  wrote:

    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.

    I struggled also with the "table tuning" feature in DPA 12.0.x.  The outcome of a support case was to grant the following:

    GRANT CONNECT ANY DATABASE TO [xxx]

    From my point of view this is a more secure, future proven and elegant solution than granting read access on each database and data.

    E.g. each new created database is automatically "visible", the access to possible confidential business data is not granted, etc..

    SQL Server permissions for DPA monitoring - SolarWinds Worldwide, LLC. Help and Support

    Or is there a functionality in DPA 12.0.x which require explicit the db_datareader role?

    regards

    fabian

  • Thanks for your reply. Did you grant the privileges in the SQL Server permissions article and then granted connect any database in addition to those? No, there is not a requirement for db_datareader role in DPA.

  • darichar  wrote:

    Thanks for your reply. Did you grant the privileges in the SQL Server permissions article and then granted connect any database in addition to those? No, there is not a requirement for db_datareader role in DPA.

    I went through the document SQL Server permissions for DPA monitoring - SolarWinds Worldwide, LLC. Help and Support  and execute after the successful instance registration the following:

    ALTER SERVER ROLE [sysadmin] DROP MEMBER [xxx]

    GRANT VIEW ANY DATABASE TO [xxx]
    GRANT VIEW SERVER STATE TO [xxx]
    GRANT VIEW ANY DEFINITION TO [xxx]
    GRANT ALTER ANY EVENT SESSION TO [xxx]

    GRANT CREATE TABLE TO [xxx]
    GRANT SELECT ON msdb.dbo.sysjobs TO [xxx]
    GRANT SELECT ON msdb.dbo.sysjobhistory TO [xxx]


    -- added / required for new table advisor in 12.x and alert database freespace
    GRANT CONNECT ANY DATABASE TO [xxx]

    If one of the grants is unnecessary please let me know.

  • Thanks to both of your for your replies!

    It appears that the 'GRANT CONNECT ANY DATABASE TO [xxx]' syntax will only be useful if the monitored instance is SQL Server 2014 or above (since it was introduced with that version).  I found some information that claimed 'GRANT VIEW ANY DEFINITION TO [xxx]' would work for previous versions (or my 2012 instance, at least) but that proved not to be true.

    At this point, it is looking like the elegant solution will be possible for 2014 (and newer) and the db_datareader version is going to be necessary for 2012 (and earlier).

  • kcthwack  wrote:

    ... and the db_datareader version is going to be necessary for 2012 (and earlier).

    You have just to have in mind that if you grant [db_datareader] to the DPA monitoring user that this user can read all business data in the database itself. This could be an issue depends on the internal/company policies.

    darichar

    Did you ever thought about to use (server/db) roles for the DPA monitor user in the MS SQL server environment instead of granting the privs directly to the login?

  • If you want to use the "kill session" feature, you also need to grant ALTER ANY CONNECTION.