remidian.com

Getting around in the mssql environment

Whoami

In MSSQL (2000) there’s not an easy way to see in which database you are. Script below creates a view ‘whoami’ that gives you the information;

create view dbo.whoami
as
select @@spid as '@@spid',
       cast( user as varchar(12)) as 'user',
       cast( p.dbid as varchar(6)) + ':' + cast( d.name as varchar(14)) as 'database',
       cast( p.loginame as varchar(16)) loginame,
       cast( p.hostname as varchar(16)) as 'hostname',
       cast( p.nt_username as varchar(16)) nt_username
   from master.dbo.sysprocesses p left join master.dbo.sysdatabases d
      on d.dbid = p.dbid
   where spid = @@spid
 
go
 
grant select on dbo.whoami to public
go
 

1> select * from whoami
2> go
 @@spid user         database              loginame         hostname         nt_username
 ------ ------------ --------------------- ---------------- ---------------- ----------------
     51 dbo          1:master              NYC01\\remivisser NYC01            remivisser
 
(1 row affected)
1>