/ ************************************************** *********************************************************** *************
The following SP is the IP and HostName returned to all clients, with the purpose of returning the connection between a certain point in time via JOB.
I have written this script that there is often some unauthorized clients. I connect to SQL Server through SQL Server's client, so I can define a Job running once every 30 minutes, and writes a log file written. This can probably record which clients are connected to SQLServer, of course, you can modify this script to return more information, such as CPU, Memory, Lock ....
Author: Huangshan Guangmingding
Mail: leimin@jxfw.com
Version: 1.0.0
Date: 2004-1-30
(For reprint, please indicate the source!)
*********************************************************** *********************************************************** ***** /
Create Proc USP_GETCLIENT_INFOR As Set NoCount ON
Declare @rc intdeclare @Rowcount Int
SELECT @ rc = 0select @ rowcount = 0
BEGIN - // Create Temp Table, Save sp_who information Create Table #tspid (Spid Int Null, Ecid Int Null, Status Nchar (60) Null, Loginname Nchar (256) Null, Hostname Nchar (256) Null, BLK Bit Null, DBNAME NCHAR (256) NULL, CMD NCHAR (32))
- // Create Temp Table Save All Sql Client IP and Hostname and Login Timecreate Table # uSerip ([Id] Int Identity (1, 1), TXT VARCHAR (1000),
- // Create Result Table To Return RecordSetcreate Table #Result ([ID] Int Id Identity (1, 1), Clientip Varchar (1000), Hostname Nchar (256), login_time datetime default (getdate ())
) - // Get host name by Exec Sp_who, INSERT #TSPID from sp_who, INSERT INTO #TSPID (SPID, ECID, STATUS, LOGINNAME, HOSTNAME, BLK, DBNAME, CMD) EXEC SP_WHO
Declare @cmdstr varchar (100), @Hostname nchar (256), @userip varchar (20), @sendstr varchar (100)
- // Declare A CURSOR from table #tspiddeclare Tspid Cursor for Select Distinct Hostname from #tspid with (NOLOCK) Where SPID> 50 for read Only
open tspid fetch next from tspid into @hostname While @@ FETCH_STATUS = 0 begin select @ cmdStr = 'ping' rtrim (@hostName) insert into #userip (txt) exec master..xp_cmdshell @cmdStr select @ rowcount = count (id ) from #userIPif @ RowCount = 2 - // no IP feedback package begin insert into #Result (ClientIP, hostname) values ( 'Can not get feedback package from Ping!', @ hostname) end if @RowCount> 2 begin select @ Userip = Substring (TXT, Charindex ('[', TXT) 1, Charindex (']', txt) -Charindex ('[', txt) -1) from # urip where txt like 'pinging%' Insert Into #Result (Clientip, Hostname) VALUES (@ userip, @ hostname) end select @rc = @@ error if @ rc = 0 Truncate Table # @ @ @ @ c c # urip table
Fetch next from tspid Into @Hostname end
Close TspidDeallocate TSPID
Select * from #Result with (NOLOCK)
Drop Table #TSPID Drop Table # uripdrop table #ResultendGoExec usp_getclient_infor
