December 6, 2010 at 7:58 am
Hi All,
I am using online database... How do i trace the user system IP (Means :- some one user is using my database so i want his system IP or MAC address details)
Regards
Satish Saidapur
December 6, 2010 at 8:33 am
one of the DMV's has the IP address in 2205 and above:
select host_name(),app_name(),client_net_address from sys.dm_exec_connections where session_id = @@spid
i tend to use this snippet for auditing:
SELECT
getdate() as EventDate,
DB_NAME() As dbname,
HOST_NAME() As hostname,
APP_NAME() as applicationname,
OBJECT_NAME(@@PROCID) as procedurename,
USER_ID() as userid,
USER_NAME() as unsername,
SUSER_ID() as suserid,
SUSER_SNAME() as susername,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address as ipaddress
from sys.dm_exec_connections where session_id = @@spid
Lowell
December 6, 2010 at 8:49 am
Hi Lowell,
Could you explain me please...........
December 6, 2010 at 9:32 am
are you familiar with connections to the database?
every connection is assigned a uniqueID..the @spid...they are typically an integer starting at 51, and goes up.
in any of your database code, you could use the @@spid of the connection to run the queyr examples i laid out....from there you could audit the info to a table or whatever you wanted.
if you just want to see everyone's IP that is connected right now, you could add this proc and simply run sp_who3 ; this gives the IP as well as teh last command they executed:
/***************************************************************************************************************
*Routine Name: [dbo].[sp_who3]
*Purpose: Return SPID information from SQL Server
*Used by: Admins
*Inputs: @IsUserProcess (0,1),@CurrentSpid (0,Null)
*Returns:
*Test Code: EXEC sp_who3
***************************************************************************************************************/
--#############################################################################
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.indexes, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_who3'
--#############################################################################
CREATE Procedure sp_who3
@IsUserProcess TinyInt = 1
,@CurrentSpid TinyInt = 0
As
Begin
Set NoCount On
/*
Test Code
------------------------------------------
Declare @IsUserProcess TinyInt
,@CurrentSpid TinyInt
Select @IsUserProcess = 0
,@CurrentSpid = 0
*/
Select es.session_id As SPID
,ROW_NUMBER() over (partition by es.session_id order by es.login_time) As SubProcessID
,DB_NAME(Coalesce(er.database_id,tl1.resource_database_id,' - ')) As DBName
,Coalesce(ot.task_state,es.status,' - ') As TaskStatus
,es.login_name As LoginName
,Coalesce(ec.client_net_address,' - ') As IPAddress
,Coalesce((SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
FOR XML PATH(''), TYPE
),'') As QueryText
,Coalesce(er.wait_type,wt.wait_type,er.last_wait_type,' - ') As WaitType
,Coalesce(es.host_name,' - ') As HostName
,Coalesce(tl.request_session_id,'') As BlockedBy
,Coalesce((SELECT p.text
FROM (SELECT MIN(sql_handle) As sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = tl.request_session_id
) As rb
CROSS APPLY
(SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(rb.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
),'') As BlockingText
,Coalesce(es.program_name,' - ') As ProgramName
,Coalesce(es.client_interface_name,' - ') As ClientInterface
,Coalesce(es.host_process_id,' - ') As UserProcessID
,es.login_time As LoginTime
,es.last_request_start_time As LastBatch
,es.total_elapsed_time *.001 As SessionElapsedTime
,es.total_scheduled_time * .001 As CPUTime
,es.memory_usage As Num8kPages
,Coalesce(ec.num_reads,'') As NumReads
,Coalesce(ec.num_writes,'') As NumWrites
,Coalesce(er.open_transaction_count,st.TranCount,0) As OpenTranCount
,Coalesce(dt.lockcount,0) As LockCount
From sys.dm_exec_sessions es
Left Outer Join sys.dm_exec_connections ec
On ec.session_id = es.session_id
Left Outer Join sys.dm_os_waiting_tasks wt
On wt.session_id = es.session_id
Left Outer Join sys.dm_os_tasks ot
On es.session_id = ot.session_id
Left Outer Join sys.dm_tran_locks tl
On wt.blocking_session_id = tl.request_session_id
Left Outer Join sys.dm_tran_locks tl1
On ec.session_id = tl1.request_session_id
Left Outer Join sys.dm_exec_requests er
On tl.request_session_id = er.session_id
Left Outer Join (
Select request_session_id,COUNT(request_session_id) As LockCount
From sys.dm_tran_locks
Group By request_session_id
) dt
On ec.session_id = dt.request_session_id
Left Outer Join (
Select session_id,COUNT(session_id) As TranCount
From sys.dm_tran_session_transactions
Group By session_id
) st
On ec.session_id = st.session_id
Where es.is_user_process >= (Case when @IsUserProcess = 0 Then 0 Else 1 End)
And es.session_id <> (Case when @CurrentSPID = 0 Then 0 Else @@SPID End) --@@SPID if current Spid is to be excluded
End
Lowell
December 6, 2010 at 9:36 am
also you said "online database"...maybe you meant you are using a web server and a database together?
in that case, the web server can return the IP Address and mac address from the server variables:
in classic asp, this would present the list, for example...you could google how to do the same for asp.net or php or whatever you are using.
For Each Key in Request.ServerVariables
Response.Write Key & ": " & Request.ServerVariables(Key) & "
"
Next
Lowell
December 6, 2010 at 10:19 pm
Hi Lowell,
Ya i got your point, I will check once & get back to you......
Thank you
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply