I'm trying to run Adam Machanic's sp_WhoIsActive in AWS RDS for SQL Server. When I run as the designated master account, say "TheAdmin", it works. When I run as another user, say "AlmostAdmin", whom I've made a db_owner on the target DB, say "MyDB", it fails with permission errors like so:

Msg 297, Level 16, State 1, Line 49
The user does not have permission to perform this action.
Msg 297, Level 16, State 1, Line 342
The user does not have permission to perform this action.

Note that, because you can't create any objects in the master DB in RDS, I had to create sp_WhoIsActive in the user DB MyDB, as stated.

Anybody had any luck making this work in RDS? Thanks!

Other things I've tried:

  • Granted VIEW SERVER STATE to "AlmostAdmin" and added him to server-roles processadmin and setupadmin to mirror "TheAdmin".
  • Added "AlmostAdmin" to all other elevated DB roles in MyDB (yeah, I know, shouldn't make a difference, just grasping at straws).

I guess I'll answer myself, since it's working...

The required permissions seem to be as follows:

  1. "AlmostAdmin" is a SQL Login in the public server role, GRANTed securables "Connect SQL" and "View Server State".
  2. 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

--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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.