In some environments it may not be feasible for Spotlight to connect to the Amazon RDS for SQL Server instance server with an account that is a member of the sysadmin server role. The following instructions are provided for these environments. Note that there are known issues with this procedure as documented below.

Ensure the TrustedUser default database is set to master

From SSMS (SQL Server Management Studio): Security | Logins | ‘Monitoring account’ | Properties | General | Default database select master as the default database.

Run this script to grant permissions to TrustedUser

To resolve these permission issues, the user needs to run the script below by logging in to the database as a user who already has these privileges (by default, the master user for Amazon RDS has all these privileges).

The user to whom the privileges need to be granted (TrustedUser) should be specified in the script as the value of the username variable (line 9).

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);
DECLARE @username varchar(128)
DECLARE @databases CURSOR;
DECLARE @dbname varchar(128);
Declare @dbownerflag TABLE (exist INT)
Declare @temp TABLE (exist INT)
SET @username = 'testuser'

USE [master]

SET @SQL = 'GRANT VIEW SERVER STATE TO ' + QUOTENAME(@username) + ';' + CHAR(13);
SET @SQL = @SQL + 'GRANT VIEW ANY DEFINITION TO ' + QUOTENAME(@username) + ';' + CHAR(13);
SET @SQL = @SQL + 'GRANT ALTER TRACE TO ' + QUOTENAME(@username) + ';' + CHAR(13);
SET @SQL = @SQL + 'GRANT ALTER ANY EVENT SESSION TO ' + QUOTENAME(@username) + ';' + CHAR(13);
SET @SQL = @SQL + 'ALTER SERVER ROLE processadmin ADD MEMBER ' + QUOTENAME(@username) + ';' + CHAR(13);
EXEC sp_executesql @SQL;

use msdb

SET @SQL = 'select 1 from sys.database_principals where type != ''R'' and name = ''' + @username + '''';
INSERT INTO @temp EXEC sp_executesql @SQL;

IF NOT EXISTS(select 1 from @temp)
	BEGIN
		SET @SQL = 'CREATE USER '+ QUOTENAME(@username) + ' FOR LOGIN ' +QUOTENAME(@username) + ';';
		EXEC sp_executesql @SQL;
		PRINT '>>			[' + @username + '] user didn''t exist in msdb, [' + @username + '] user added in msdb database			<<';
	END

SET @SQL = 'GRANT SELECT ON dbo.sysjobs TO ' + QUOTENAME(@username) + ';' + CHAR(13);
SET @SQL = @SQL + 'GRANT SELECT ON dbo.sysjobhistory TO ' + QUOTENAME(@username) + ';' + CHAR(13);
SET @SQL = @SQL + 'GRANT SELECT ON msdb.dbo.sysjobactivity TO ' + QUOTENAME(@username) + ';' + CHAR(13);

EXEC sp_executesql @SQL;
EXECUTE sp_addrolemember N'SQLAgentUserRole' , @username;
PRINT '>>			Granted missing critical permissions required to monitor database server by Spotlight to ['+ @username + '] database user			<<' + CHAR(13);;
DELETE FROM @temp;

PRINT '>>			Explicitly granting access to [' + @username + '] to individual databases			<<' + CHAR(13);

BEGIN
    SET @databases = CURSOR FOR
    SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1 AND name not in ('master', 'tempdb', 'msdb', 'rdsadmin')

    OPEN @databases 
    FETCH NEXT FROM @databases INTO @dbname

	WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @SQL = 'use '+ QUOTENAME(@dbname) + ' select 1 from sys.database_principals where type != ''R'' and name = ''' + @username + '''';
		INSERT INTO @temp EXEC sp_executesql @SQL;

		SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'select 1 where IS_ROLEMEMBER (''db_owner'') = 1;';
		INSERT INTO @dbownerflag EXEC sp_executesql @SQL;

		IF NOT EXISTS(select * from @dbownerflag)
			BEGIN
				PRINT '>>			Can''t grant access to [' + @dbname + '] database as ['+current_user+ '] doesn''t have db_owner role in [' + @dbname + '] database	  		<<';
			END
		ELSE
			BEGIN
				IF EXISTS(select 1 from @temp)
					BEGIN
						PRINT '>>			['+@username +'] is already having access to [' + @dbname + '] database	  		<<';
						SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'ALTER ROLE db_owner ADD MEMBER '+ QUOTENAME(@username);
						PRINT '>>			Granting db_owner role for [' + @dbname + '] database to ['+@username +'] user			<<';
						EXEC sp_executesql @SQL;
					END
				ELSE
					BEGIN
						SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'CREATE USER '+ QUOTENAME(@username) + ' FOR LOGIN ' +QUOTENAME(@username) + ';';
						PRINT '>>			Granting access to [' + @dbname + '] database to ['+@username +'] user			<<';
						EXEC sp_executesql @SQL;
						SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'ALTER ROLE db_owner ADD MEMBER '+ QUOTENAME(@username);
						PRINT '>>			Granting db_owner role for [' + @dbname + '] database to ['+@username +'] user			<<';
						EXEC sp_executesql @SQL;
					END
			END
			FETCH NEXT FROM @databases INTO @dbname
			DELETE FROM @temp;
			DELETE FROM @dbownerflag;
		END; 

    CLOSE @databases ;
    DEALLOCATE @databases;
END;

Common problems with Amazon RDS for SQL Server connections that would be resolved by the above script include:

Require Granting Permission To resolve common issues
View Server state permission Issue while adding the connection in Spotlight
iew any definition permission Warning in Home Page Disk Storage Panel - Data Files and Log Files
Alter trace permission Error in SQL Activity | Session Trace Tab
Alter any event session Issue with Extended Events functionality
processadmin server role Error in SQLVirtualLogFilesCount procedure output
db_owner database role Warning in Database Drilldown | Index Tab | Index Details Section
Select permission on dbo.sysjobs Warning in Support Services drilldown - SQL Agent Jobs tab
Select permission on dbo.sysjobhistory Warning in Support Services drilldown - SQL Agent Jobs tab
Select permission on dbo.sysjobactivity Warning in Support Services drilldown - SQL Agent Jobs tab

To grant permission to new databases in special case:

In Amazon RDS for SQL Server, the sysadmin role, typically a powerful administrative role in standard SQL Server, is not accessible to any user, including the master user. This fundamental difference means that databases created by a specific user are inherently private to that user.

As a result, if a database is created by a user different from the one configured in Spotlight, the user accessing Spotlight will not be able to view the data in that database. This is because the creator of the database retains exclusive access unless explicit permissions are granted.

To enable a user to access a database created by another user, the database owner must execute a SQL script to grant the necessary permissions.

In line 9 of the script, specify the username (TrustedUser) for whom access to individual databases should be granted.

To identify which user has the db_owner role in a specific database, you can run the query provided below on that database. Note that to execute the query, the user must have access to the database.

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;

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);
DECLARE @databases CURSOR;
DECLARE @dbname varchar(128);
DECLARE @username varchar(128)
Declare @temp TABLE (exist INT)
Declare @dbownerflag TABLE (exist INT)
SET @username = 'testuser'
BEGIN
    SET @databases = CURSOR FOR
    SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1 AND name not in ('master', 'tempdb', 'msdb', 'rdsadmin')

    OPEN @databases 
    FETCH NEXT FROM @databases INTO @dbname

	WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @SQL = 'use '+ QUOTENAME(@dbname) + ' select 1 from sys.database_principals where type != ''R'' and name = ''' + @username + '''';
		INSERT INTO @temp EXEC sp_executesql @SQL;

		SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'select 1 where IS_ROLEMEMBER (''db_owner'') = 1;';
		INSERT INTO @dbownerflag EXEC sp_executesql @SQL;

		IF NOT EXISTS(select * from @dbownerflag)
			BEGIN
				PRINT '>>			Can''t grant access to [' + @dbname + '] database as ['+current_user+ '] doesn''t have db_owner role in [' + @dbname + '] database	  		<<';
			END
		ELSE
			BEGIN
				IF EXISTS(select 1 from @temp)
					BEGIN
						PRINT '>>			['+@username +'] is already having access to [' + @dbname + '] database	  		<<';
						SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'ALTER ROLE db_owner ADD MEMBER '+ QUOTENAME(@username);
						PRINT '>>			Granting db_owner role for [' + @dbname + '] database to ['+@username +'] user			<<';
						EXEC sp_executesql @SQL;
					END
				ELSE
					BEGIN
						SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'CREATE USER '+ QUOTENAME(@username) + ' FOR LOGIN ' +QUOTENAME(@username) + ';';
						PRINT '>>			Granting access to [' + @dbname + '] database to ['+@username +'] user			<<';
						EXEC sp_executesql @SQL;
						SET @SQL = 'use '+ QUOTENAME(@dbname) + CHAR(13) + 'ALTER ROLE db_owner ADD MEMBER '+ QUOTENAME(@username);
						PRINT '>>			Granting db_owner role for [' + @dbname + '] database to ['+@username +'] user			<<';
						EXEC sp_executesql @SQL;
					END
			END
			FETCH NEXT FROM @databases INTO @dbname
			DELETE FROM @temp;
			DELETE FROM @dbownerflag;
		END; 

    CLOSE @databases ;
    DEALLOCATE @databases;
END;

Known issues with this script

The following functions require sysadmin privileges and, therefore, encounter issues when using the Support Services Drilldown in the SQL Agent Jobs tab.

Due to the absence of the sysadmin role for any user, only the creator of a job can run that job.

This means that SQL Agent Jobs not created by the monitoring database user can only be viewed in the drilldown but cannot be started. (The monitoring database user refers to the user used to add the connection in the Spotlight application.)