The user account used for monitoring can be a Windows authenticated account (if the Database Agent is running on Windows) or SQL Server authenticated (if AppDynamics Database Visibility is running on Windows or Linux). 

Minimum Permissions Required for SQL Server Logon

You can use the procedure to create a SQL Server user with the minimum permissions required. 

Use the following to create a SQL Server logon user that provides the minimal level of permissions required in order to gain full AppDynamics Database Visibility/SQL Server functionality.

Master users created in the AWS RDS instance are the instance masters. If you want to provide access to a local user for monitoring, use the same master user and create a custom monitoring role and assign the same to the user.

  1. Using SQL Server Management Studio, create a new login for the AppDynamics SQL Server Database Collector, such as DBMon_Agent_User. 

  2. (For users other than master user) Execute these commands as a batch from a query window in Management Studio. This example shows grants to the role assigned to DBMon_Agent_User to monitor the database:

    USE master
GO
CREATE SERVER ROLE monitoring_role
USE master
GO
ALTER SERVER ROLE DBMon_Agent_User ADD MEMBER Monitoring_User
GRANT CONNECT SQL TO Monitoring_User
GRANT VIEW ANY DATABASE TO Monitoring_User
GRANT VIEW ANY DEFINITION TO Monitoring_User
GRANT VIEW SERVER STATE to Monitoring_User
    SQL

  3. From the User Mapping tab, map the new user to the master and msdb databases. 

    Viewing Object Information

    To view object information on the Database > Objects Browser, map the monitoring user to the databases of interest.
  4. Once you have created the login, give the following privileges to the user, substituting DBMon_Agent_User with the name you specified on the Login - New panel:

You can execute the following as a batch from a query window in Management Studio. The example shows grants to DBMon_Agent_User; remember to change this if you have set up a different login.

use master
ALTER SERVER ROLE processadmin ADD MEMBER DBMon_Agent_User;
GRANT VIEW ANY DATABASE TO DBMon_Agent_User;
GRANT VIEW ANY definition to DBMon_Agent_User;
GRANT VIEW server state to DBMon_Agent_User;
CODE


where DBMon_Agent_User is the name of the SQL Server user account specified in Create New Collector, Connection Details, Username field. 
To generate an execution plan on AWS RDS, you need additional permissions. The SHOWPLAN permission must be provided explicitly for each database:


USE <DB NAME>
go
GRANT SHOWPLAN to DBMon_Agent_User;
go
CODE