11 Replies Latest reply on Sep 20, 2018 3:10 PM by kcthwack

    Removing sysadmin from monitoring account

    kcthwack

      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?

        • Re: Removing sysadmin from monitoring account
          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.

              • Re: Removing sysadmin from monitoring account
                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;

                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;

                  • Re: Removing sysadmin from monitoring account
                    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.

                      • Re: Removing sysadmin from monitoring account
                        fabian.s

                        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

                          • Re: Removing sysadmin from monitoring account
                            darichar

                            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.

                              • Re: Removing sysadmin from monitoring account
                                fabian.s

                                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.

                                1 of 1 people found this helpful
                                  • Re: Removing sysadmin from monitoring account
                                    kcthwack

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

                                      • Re: Removing sysadmin from monitoring account
                                        fabian.s

                                        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?

                                      • Re: Removing sysadmin from monitoring account
                                        angela.lanz

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

                          • Re: Removing sysadmin from monitoring account
                            kcthwack

                            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]