Run this script to grant permissions to the user

To this SQL script:

  1. Replace all occurrences of TrustedUser with the name of the user.
  2. There are commented lines at the end of the script; un-comment these lines as appropriate for your environment.
  3. Run the script as master user.
USE [master]
GO

GRANT VIEW SERVER STATE TO [TrustedUser];
GRANT VIEW ANY DEFINITION TO [TrustedUser];

GRANT CREATE ANY DATABASE TO [TrustedUser] WITH GRANT OPTION;
GRANT VIEW ANY DATABASE TO [TrustedUser] WITH GRANT OPTION;

ALTER SERVER ROLE processadmin ADD MEMBER TrustedUser;
GRANT VIEW ANY DATABASE TO [TrustedUser];
GRANT VIEW ANY definition to [TrustedUser];
GRANT VIEW server state to [TrustedUser];

GRANT ALTER TRACE TO [TrustedUser];



USE [msdb]
GO

CREATE USER [TrustedUser] FOR LOGIN [TrustedUser];
GO

GRANT EXECUTE ON msdb.dbo.rds_backup_database TO [TrustedUser];
GRANT EXECUTE ON msdb.dbo.rds_restore_database TO [TrustedUser];
GRANT EXECUTE ON msdb.dbo.rds_task_status TO [TrustedUser];
GRANT EXECUTE ON msdb.dbo.rds_cancel_task TO [TrustedUser];

GRANT SELECT ON dbo.sysjobs TO [TrustedUser];
GRANT SELECT ON dbo.sysjobhistory TO [TrustedUser];
GRANT SELECT ON msdb.dbo.sysjobactivity TO TrustedUser;

EXECUTE	sp_addrolemember N'SQLAgentUserRole' , N'TrustedUser';
go


USE [master]
GO


declare @dbnumber int;
declare @dbname sysname;
declare @use nvarchar(4000);
declare @Quest_dblist table (row int identity, name sysname);

insert into @Quest_dblist (name)
	select d.name
		from sys.databases d
		where	d.user_access = 0	and has_dbaccess(d.name) <> 0
		and		d.state = 0			and d.is_auto_close_on = 0
		and		d.is_read_only = 0	and	is_distributor = 0
		and		d.name not in ('master', 'model', 'msdb', 'tempdb', 'SSISDB', 'rdsadmin', 'jackie-ora-3');
set @dbnumber = @@rowcount;

while @dbnumber > 0
begin
	select @dbname =name from @Quest_dblist where row = @dbnumber

	set @use = N'USE ' + quotename(@dbname)
	+ N'CREATE USER [TrustedUser] FOR LOGIN [TrustedUser]';
	exec (@use);

	set @use = N'USE ' + quotename(@dbname)
	+ N'GRANT SHOWPLAN to TrustedUser';
	exec (@use)

	set @dbnumber = @dbnumber - 1;
end
GO

Known issues with this script

The following monitoring functions require sysadmin privileges; hence have known issues and will not work even after using the script:

  • Number of virtual log files (VLFs)
  • Jobs