Tutorials to .com

Tutorials to .com » Database » Sqlserver » -SQL access to the current connected client IP and machine name

-SQL access to the current connected client IP and machine name

Print View , by: iSee ,Total views: 14 ,Word Count: 565 ,Date: Sun, 23 Aug 2009 Time: 4:07 AM

-SQL access to the current connected client IP and machine name

/************************************************* ************************************************** **************

SP is to return the following to all client IP and HOSTNAME, can be aimed at a particular point in time JOB return connections of the CLIENT.

At that time I wrote this script is aimed at some of the regular client does not have the authorization by the CLIENT Even sqlserver received SQLSERVER, so I can define a JOB to run 30 minutes every time the stored procedure, and the contents of a document written by a LOG This will probably record in the CLIENT which connect SQLSERVER, of course, you can modify this script can make it back to more information, such as CPU, MEMORY, LOCK ....

Author: Huangshan

mail: leimin@jxfw.com

version: 1.0.0

date :2004-1-30

(For a reprint, please indicate the source!)

************************************************** ************************************************** *****/

Create proc usp_getClient_infor
as
set nocount on

Declare @ rc int
Declare @ RowCount int

Select @ rc = 0
Select @ 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 time
Create table # userip (
[id] int identity (1,1),
txt varchar (1000),
)

- / / Create result table to return recordset
Create table # result (
[id] int 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 # tspid
declare 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 # userIP


if @ 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 # userIP
where txt like Pinging%

insert into # Result (ClientIP, hostname) values (@ userIP, @ hostname)
end
select @ rc = @ @ error
if @ rc = 0
truncate table # userip - / / clear # userIP table

fetch next from tspid into @ hostname
end

close tspid
deallocate tspid

select * from # result with (nolock)

drop table # tspid
drop table # userip
drop table # result
end
go
exec usp_getClient_infor


Microsoft SQL Server Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.