How to find out user name and machine name to access to SQL server

Question

My work company has a MSSQL server 2005. I have two questions about finding out current log user and any way to send out a warning message:

First question is if there is any T-SQL or SP available to find out current login user name and machine name. If the user is using SQL server sa name to remotely access to SQL server, is there any way to find out that user's windows name (the name to log to the windows)?

My next question is that if I can get the user name or id, is there any way to send out a warning message such as "currently SQL server is clean up or backup, please do not log in at this time". I guess it may be difficult. I may have to send an email out to the user.

The SQL server is only accessible in the company. The SQL server has a list of users as login users: windows users, SQL users and sa.

Answer 1

SELECT SUSER_SNAME(), HOST_NAME()

I the connection is "sa" (or any other SQL login) then you can't find the domain/windows user name. SQL Server only knows it's "sa".

HOST_NAME may not be reliable either, it can be set in the connection string. MS Access for example does not support this.

You could backtrack via client_net_address in sys.dm_exec_connections and match MAC address to IP and find out who is logged on...

Answer 2

An easy way to find out both host and user is

EXEC sp_who2;

where you get some other information that can be good to know, as if the user is active and so on... It does not resolve the issues that gbn announced.

Answer 3

For a more visual representation of grabbing a list of users and what Databases they are in open up Microsoft SQL Server Management Studio. In there you can click on the Management folder then Double Click on Activity Monitor.

This will give you a graphic display similar to running sp_who in a query. The user tab will give you a clue to what type of account they have.

You can also run quick filter on that list to narrow down your search. e.g. via a database.

Answer 4

Thanks for all your suggestions first. I tried all the methods and I think Joakim Backman's method meet my need. Here is summary of what I find out.

• Query of sys.syslogins only list login information. The accdate does not give the current login user timestamp. I tried to login from another application to my SQL and this query does not list the login.
• SELECT SUER_SNAME(), HOST_NAME() only list one user on SQL server. For example, I login in as my name to SQL server. The result this query only list my name and machine name. This query does not list current user on the SQL server.
• exec sp_who2 lists information I need. It lists current user name machine name, active status, db name users access, and command used.

In order to get the information I use in SP, I have to filter and join the information with other tables such as emails. Here is the codes I use:

DECLARE @retTable TABLE (
 SPID int not null
 , Status varchar (255) not null
 , Login varchar (255) not null
 , HostName varchar (255) not null
 , BlkBy varchar(10) not null
 , DBName varchar (255) null
 , Command varchar (255) not null
 , CPUTime int not null
 , DiskIO int not null
 , LastBatch varchar (255) not null
 , ProgramName varchar (255) null
 , SPID2 int not null
 , REQUESTID INT
)  

INSERT INTO @retTable EXEC sp_who2  

SELECT Status, Login, HostName, DBName, Command, CPUTime, ProgramName -- *
  FROM @retTable
  --WHERE Login not like 'sa%' -- if not intereted in sa
  ORDER BY Login, HostName

Answer 5

I have use the above sp that recommend by David. It works great for my development Database, but can't be run at Live Database.

Is this due to access rights or security issues?