显示连接到SQLserver上的用户IP及名称(转载)

最新推荐文章于 2021-03-11 06:34:39 发布

原创最新推荐文章于 2021-03-11 06:34:39 发布 · 1.1k 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文介绍了一个SQL存储过程，该过程用于收集SQL Server中所有客户端的连接信息，包括主机名、客户端IP地址等，并将这些信息存储在临时表中以便进一步处理。

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

标签