Cannot access table tuning advisors when monitoring a SQL Server instance on Amazon RDS

You are monitoring a SQL Server database instance that runs on Amazon Relational Database Service (Amazon RDS). Your account has admin privileges. But one of the following happens:

• When you open a Table Tuning Advisor, DPA displays the following message:

  You are not authorized to perform this action. Contact your DPA administrator.

• When you open the Tuning tab to view Query Advisors and Table Tuning Advisors for the selected database instance, no advisors are available and DPA displays a message about the DPA monitoring user's permissions. 

The DPA monitoring user does not have the privileges to access the required database view. 

Complete the following steps to grant the required permissions:

1. Connect to the monitored SQL Server RDS instance as the master user. 

2. Execute the following stored procedure, replacing <user_login> with the DPA monitoring user name:

   EXEC sp_MSforeachdb '
    DECLARE @name VARCHAR(500)
    SET @name=''?''
    BEGIN
    USE [?]
    IF NOT EXISTS (select * from sys.database_principals WHERE name = N''<user_login>'')
    CREATE USER <user_login> FOR LOGIN <user_login>;
    EXEC sp_addrolemember ''db_owner'',''<user_login>''
    END'

Note: If you add an additional database after executing this stored procedure, you must execute it again.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.