How to resolve missing data in databases drilldowns for Amazon RDS for SQL Server Connection? (4378028)

As Amazon RDS for SQL Server Connection, Amazon doesn’t provide sysadmin role to any database user, user can’t give sysadmin role to any database user to provide maximum database privileges to that user. Due to lack of access to this database role, in Amazon RDS for SQL Server connection any database user can’t fetch other databases** details and tables, indexes information if the other databases are not created by that specific database user.
This happens because any user created database implicitly gives ‘db_owner’ role for that database to the creator of that database only, other database users can’t access those database unless explicitly access is given to those database users or the other database user has ‘sysadmin’ role.
As for Amazon RDS for SQL Server connection, user can’t have the ‘sysadmin’ role, we need to explicitly give ‘db_owner’ role for any user created database to the database users who are not the creator of those databases.

Amazon doesn’t provide sysadmin role to any database user, user can’t give sysadmin role to any database user to provide maximum database privileges to that user.

WORKAROUND:

1. Download the attached script
2. Modify the target user name in line number 9 (replacing the ‘testuser’ with the target database username.
3. Login as the Database creator user
4. Run the script to resolve the issue for those databases where the logged in user has the ‘db_owner’ role. For other databases you will need to find out which user has the ‘db_owner’ role and log in using that user and repeat the process.

To fetch the list of database users who are having db_owner role on the corresponding database, user can use below query:

use databasename;

DECLARE @dbname varchar(128);
DECLARE @SQL NVARCHAR(MAX);
SET @dbname = (SELECT DB_NAME())

SET @SQL = 'select name as "database users with db_owner role in '+@dbname+' database" from sys.database_principals where principal_id in 
(select rm.member_principal_id from sys.database_principals dp join sys.database_role_members rm ON dp.principal_id = rm.role_principal_id  where name = ''db_owner'' and type = ''R'')
AND type <> ''R'''

EXEC sp_executesql @SQL;

NOTE: 
In Amazon RDS for SQL Server, user can’t get information related to model database, this is a limitation from Amazon side, not a limitation of Spotlight Application.