Sql-server – Use sp_WhoIsActive in AWS RDS (MSSQL)

amazon-rdspermissionssp-whoisactivesql server

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

Best Answer

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

Related Solutions

SQL Server – List All Sessions from Current User

SQL Server is designed (along with most db engines) with security in mind the main areas you'll look at for viewing users are

sp_who [spid]|[login]
sp_who2 [spid]|[login]
select * from sys.sysprocesses

All of these commands are accessible for all users (all are views) but the views are restricted to just your current spid unless you have the permission 'view server state' which can be granted on a database by database level.

A user is granted the basic permissions to view their own connection as it is information about yourself (Note that this will always report back that you are running a select command as that's what you're doing at the time)

If you have the 'view server state' permission then you can run a query such as:

select * from sys.sysprocesses where loginame = current_user

(Or create a procedure to run that) NOTE: this will NOT work for any sysadmin account as their current_user is always 'dbo'

EDIT: Warning, view server state will grant permission to view anyone who is connected, not just people with the current user name so you may want to check around what potential additional security you wish to put in place for that if need be

SQL Server – Error Running Stored Proc sp_BlitzIndex v4.2

You also need VIEW SERVER STATE permission in order to run sp_BlitzIndex. This is because it is querying DMVs.

Best Answer

Related Solutions

SQL Server – List All Sessions from Current User

SQL Server – Error Running Stored Proc sp_BlitzIndex v4.2

Related Question