SQL Server – List All Sessions from Current User

sql serversql-server-2008-r2

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?

Best Answer

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

Related Question