• Published on Sep 18 2006
  • # 14
  • T-SQL

Getting around in the mssql environment


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
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


grant select on dbo.whoami to public

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

(1 row affected)

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>