Identify top logins\users consuming resources Is there a way to identify top database users who are actually consuming high resources with their executed queries ?
There's nothing built in to SQL Server that will do this. If you don't feel like using sp_who3 and partying like it's 2002, here's another method.

Grab sp_WhoIsActive

If you just want to see what's happening now, run it like this:

EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_locks = 1

If you want to use it long term to find out what users are up to, follow the instructions here to set it up to log results to a table. I'd probably skip collecting query plans here on a busy server, that could really bloat things.

How does this help? It returns wait info, tempdb use, blocking, cpu, reads, writes, locking, memory use, along with login name, host name, and program name so you can (if you choose to log it to a table) group by connection info to aggregate resource usage over time. The aggregations won't be perfect, but it might get you close enough to what you need (for free).

If you want to spent money on it, buy a monitoring tool. Sentry One Performance Advisor and Quest Spotlight are both pretty nifty.

Hope this helps!