Use sp_WhoIsActive in AWS RDS (MSSQL)
I guess I'll answer myself, since it's working...

The required permissions seem to be as follows:


"AlmostAdmin" is a SQL Login in the public server role, GRANTed securables "Connect SQL" and "View Server State".
The login is mapped to "AlmostAdmin" user in MyDB, which is in the db_owner role with default schema dbo.


My problem seems to have been with impersonation, because even though it fails for me when I run the following--

EXECUTE AS USER = 'AlmostAdmin'
EXEC sp_WhoIsActive
REVERT


--It works for the actual user when he's logged in himself (via SSMS).  And of course then I logged in as that user and verified that it was working for me too (when using that login, instead of my "TheAdmin" master login!).

Lesson learned: don't rely on impersonation 100% for testing security/permission issues; get the target user involved to test it out as well (or if you're in a position to know the credentials, lower yourself to that level and test it, instead of completely relying on EXECUTE AS).