这个存储过程能把 当前连接某数据库的用户(数)列出来: create procedure sp_HelloEx @db_name varchar(20)='数据库名称', @loginame sysname = NULL --or 'active' asdeclare @spidlow int, @spidhigh int, @spid int, @sid varbinary(85)select @spidlow = 0 ,@spidhigh = 32767 if ( @loginame is not NULL AND upper(@loginame) = 'ACTIVE' ) begin select spid , ecid, status ,loginame=rtrim(loginame) ,hostname ,blk=convert(char(5),blocked) ,dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where spid >= @spidlow and spid <= @spidhigh AND upper(cmd) <> 'AWAITING COMMAND' return (0) endif (@loginame is not NULL AND upper(@loginame) <> 'ACTIVE' ) begin if (@loginame like '[0-9]%') -- is a spid. begin select @spid = convert(int, @loginame) select spid, ecid, status, loginame=rtrim(loginame), hostname,blk = convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where spid = @spid end else begin select @sid = suser_sid(@loginame) if (@sid is null) begin raiserror(15007,-1,-1,@loginame) return (1) end select spid, ecid, status, loginame=rtrim(loginame), hostname ,blk=convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where sid = @sid end return (0) end /* loginame arg is null */ select spid, ecid, status, loginame=rtrim(loginame), hostname, blk=convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd into #temp from master.dbo.sysprocesses where spid >= @spidlow and spid <= @spidhighselect distinct dbname , hostname from #temp where dbname = @db_namedrop table #tempreturn (0) -- sp_who GO
create procedure sp_HelloEx
@db_name varchar(20)='数据库名称',
@loginame sysname = NULL --or 'active'
asdeclare @spidlow int,
@spidhigh int,
@spid int,
@sid varbinary(85)select @spidlow = 0
,@spidhigh = 32767
if ( @loginame is not NULL
AND upper(@loginame) = 'ACTIVE'
)
begin select spid , ecid, status
,loginame=rtrim(loginame)
,hostname ,blk=convert(char(5),blocked)
,dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh AND
upper(cmd) <> 'AWAITING COMMAND' return (0)
endif (@loginame is not NULL
AND upper(@loginame) <> 'ACTIVE'
)
begin
if (@loginame like '[0-9]%') -- is a spid.
begin
select @spid = convert(int, @loginame)
select spid, ecid, status,
loginame=rtrim(loginame),
hostname,blk = convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid = @spid
end
else
begin
select @sid = suser_sid(@loginame)
if (@sid is null)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
select spid, ecid, status,
loginame=rtrim(loginame),
hostname ,blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where sid = @sid
end
return (0)
end
/* loginame arg is null */
select spid,
ecid,
status,
loginame=rtrim(loginame),
hostname,
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
into #temp
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhighselect distinct dbname , hostname from #temp where dbname = @db_namedrop table #tempreturn (0) -- sp_who
GO