List all sessions from current user

Question

I connect to SQL Server with a given user that is not an administrator. I want to get a list of all my open connections. I've found endless commands to do so but all fall in one of those categories:

1. I'm not allowed to run that command:

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

2. I only see my current session!

Is it possible to list all sessions from current user?

Answer 1

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

Answer 2

You need to have VIEW SERVER STATE permission on the user account.

GRANT VIEW SERVER STATE TO <username>;

This will need to be granted by someone with sufficient privileges and will allow you to view all sessions when you run commands like sp_who or sp_whoisactive.

If you are worried about security implications of this permission, this post may help Security and Performance implications of "View Server State"

Once you have the permissions to view server state, you can then restrict you just your user by passing that in as a parameter

EXEC sp_who [Domain\mark.sinkinson];
EXEC sp_whoisactive @filter_type = 'login', @filter = [Domain\mark.sinkinson];