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

sqlsql-serversql-server-2005tsql

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.

Best Solution

SELECT SUSER_SNAME(), HOST_NAME()

If 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" or that SQL login.

HOST_NAME may not be reliable either, it can be set in the connection string ("Application Name"). Or it could be vague eg "Microsoft Office" for by default for Access, Excel etc

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...

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Best Solution

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql-server – How to check if a column exists in a SQL Server table

Related Question