0

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?

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

  • It'd probably be more useful to have an alternative permission that allows to see all information regarding yourself but not other users; I suppose such feature does not exist. Whatever, very clear explanation. – Álvaro González Oct 16 '15 at 9:34
  • It does sound a potentially useful thing to have, although I'd presume since you're logged on you know what you're doing, (the issue is that multiple people could log in from the same account) you can always put up the suggestion to microsoft through connect.microsoft.com and it might get added one day – Ste Bov Oct 16 '15 at 10:04
1

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];
  • That was it! Now I can see all sessions. In fact, all sessions for all users. (Is there an intermediate alternative that restricts information to current user?) – Álvaro González Oct 16 '15 at 9:15
  • @ÁlvaroG.Vicario Sure, you'll need to filter for your login as a parameter. Although I'm not a huge fan of sp_who. But you get the idea... – Mark Sinkinson Oct 16 '15 at 9:22

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.